Am having difficulty getting code to run correctly - think I have a few quotation marks incorrect etc. Any help on correct syntax for following would be appreciated:
DLookup("[Construction Code]", "tblConstructionCodes", "[Walls]='" & Forms![frmQuotation]![frmSituations]![Walls] & "and [Roof]=" & Forms![frmQuotation]![frmSituations]![Roof] & "and [Floor]=" & Forms![frmQuotation]![frmSituations]![Floor] & "and [Frame]=" & Forms![frmQuotation]![frmSituations]![Frame] & "'")
11  16503  
Am having difficulty getting code to run correctly - think I have a few quotation marks incorrect etc. Any help on correct syntax for following would be appreciated:
DLookup("[Construction Code]", "tblConstructionCodes", "[Walls]='" & Forms![frmQuotation]![frmSituations]![Walls] & "and [Roof]=" & Forms![frmQuotation]![frmSituations]![Roof] & "and [Floor]=" & Forms![frmQuotation]![frmSituations]![Floor] & "and [Frame]=" & Forms![frmQuotation]![frmSituations]![Frame] & "'")
I would simplify your expression by using variables as shown below. The variables should identify the datatype that will govern format of the expression. I have assumed that all of the components of your construction codes are text data types. If that isn't the case, then the data type of the variable needs to be changed accordingly. In addition, based on the references used in the expression, I have deduced that the controls being referenced are on a subform, and have changed the reference syntax. Hope this works for you. Let me know.
Dim strWalls As String
Dim strRoof As String
Dim strFloor As String
Dim strFrame As String
strWalls = "Forms![frmQuotation]![frmSituations].Form![Walls]"
strRoof = "Forms![frmQuotation]![frmSituations].Form![Roof]"
strFloor = "Forms![frmQuotation]![frmSituations].Form![Floor]"
strFrame = "Forms![frmQuotation]![frmSituations].Form![Frame]"
DLookup("[Construction Code]", "tblConstructionCodes", "[Walls]='" & strWalls & "' & " AND [Roof] = '" & strRoof & "' & " AND [Floor] = '" & strFloor & "' & " AND [Frame] = '" & strFrame & "'")
Thanks for your reply.
OK -I have given that a go but it doesn't seem to like the single quotation marks in the Dlookup, starting with:
'" & strRoof
Any other suggestions?
I would simplify your expression by using variables as shown below. The variables should identify the datatype that will govern format of the expression. I have assumed that all of the components of your construction codes are text data types. If that isn't the case, then the data type of the variable needs to be changed accordingly. In addition, based on the references used in the expression, I have deduced that the controls being referenced are on a subform, and have changed the reference syntax. Hope this works for you. Let me know.
Dim strWalls As String
Dim strRoof As String
Dim strFloor As String
Dim strFrame As String
strWalls = "Forms![frmQuotation]![frmSituations].Form![Walls]"
strRoof = "Forms![frmQuotation]![frmSituations].Form![Roof]"
strFloor = "Forms![frmQuotation]![frmSituations].Form![Floor]"
strFrame = "Forms![frmQuotation]![frmSituations].Form![Frame]"
DLookup("[Construction Code]", "tblConstructionCodes", "[Walls]='" & strWalls & "' & " AND [Roof] = '" & strRoof & "' & " AND [Floor] = '" & strFloor & "' & " AND [Frame] = '" & strFrame & "'")
Thanks for your reply.
OK -I have given that a go but it doesn't seem to like the single quotation marks in the Dlookup, starting with:
'" & strRoof
Any other suggestions?
As I discussed above, I assumed that all of the variables were text strings, and the syntax reflects that. I changed variable data type for the roof variable to that of a numeric integer string and syntax as shown in the revised DLookup below. You will need to modify the syntax for any other numeric substring within the main string:
Dim strWalls As String
Dim intRoof As Integer
Dim strFloor As String
Dim strFrame As String
strWalls = "Forms![frmQuotation]![frmSituations].Form![Walls]"
intRoof = Forms![frmQuotation]![frmSituations].Form![Roof]
strFloor = "Forms![frmQuotation]![frmSituations].Form![Floor]"
strFrame = "Forms![frmQuotation]![frmSituations].Form![Frame]"
DLookup("[Construction Code]", "tblConstructionCodes", "[Walls]='" & strWalls & "' & " AND [Roof] = " & intRoof & " AND [Floor] = '" & strFloor & "' & " AND [Frame] = '" & strFrame & "'")
--------------------------------------------------------------------------------
Hi and thanks again. Perhaps a little more info may be helpful.
Walls, Roof, Floor, Frame are all bound combo boxes on the subform.
What I am trying to achieve is:
I have another field which I want to perform the Dlookup based on the combination of values selected in walls, roof, floor & frame.
All values are text so I would have thought the code you provided would have worked but it is still falling over on the single quotation mark as indicated in my last post.
Is there anything I can try or perhaps even another way I can achieve this result.
Ta
Hi
Pehaps this if thay are all Text fields
Dim strWalls As String
Dim strRoof As Integer
Dim strFloor As String
Dim strFrame As String
strWalls = "Forms![frmQuotation]![frmSituations].Form![Walls]"
strRoof = Forms![frmQuotation]![frmSituations].Form![Roof]
strFloor = "Forms![frmQuotation]![frmSituations].Form![Floor]"
strFrame = "Forms![frmQuotation]![frmSituations].Form![Frame]"
DLookup("[Construction Code]", "tblConstructionCodes", "[Walls]='" & strWalls & "' AND [Roof] = '" & strRoof & "' AND [Floor] = '" & strFloor & "' AND [Frame] = '" & strFrame & "'")
??
MTB
Hi and thanks again. Perhaps a little more info may be helpful.
Walls, Roof, Floor, Frame are all bound combo boxes on the subform.
What I am trying to achieve is:
I have another field which I want to perform the Dlookup based on the combination of values selected in walls, roof, floor & frame.
All values are text so I would have thought the code you provided would have worked but it is still falling over on the single quotation mark as indicated in my last post.
Is there anything I can try or perhaps even another way I can achieve this result.
Ta
Bear with me. I have done this many times, but seem to have forgotten what I did. I think the only problem is that I should not have put quotes in the assignment of variable. Try the revision below:
Dim strWalls As String
Dim strRoof As String
Dim strFloor As String
Dim strFrame As String
' quotes removed from variable assignments below because variable type defined as text string
strWalls = Forms![frmQuotation]![frmSituations].Form![Walls]
strRoof = Forms![frmQuotation]![frmSituations].Form![Roof]
strFloor = Forms![frmQuotation]![frmSituations].Form![Floor]
strFrame = Forms![frmQuotation]![frmSituations].Form![Frame]
DLookup("[Construction Code]", "tblConstructionCodes", "[Walls]='" & strWalls & "' & " AND [Roof] = '" & strRoof & "' & " AND [Floor] = '" & strFloor & "' & " AND [Frame] = '" & strFrame & "'")
Bear with me. I have done this many times, but seem to have forgotten what I did. I think the only problem is that I should not have put quotes in the assignment of variable. Try the revision below:
Dim strWalls As String
Dim strRoof As String
Dim strFloor As String
Dim strFrame As String
' quotes removed from variable assignments below because variable type defined as text string
strWalls = Forms![frmQuotation]![frmSituations].Form![Walls]
strRoof = Forms![frmQuotation]![frmSituations].Form![Roof]
strFloor = Forms![frmQuotation]![frmSituations].Form![Floor]
strFrame = Forms![frmQuotation]![frmSituations].Form![Frame]
DLookup("[Construction Code]", "tblConstructionCodes", "[Walls]='" & strWalls & "' & " AND [Roof] = '" & strRoof & "' & " AND [Floor] = '" & strFloor & "' & " AND [Frame] = '" & strFrame & "'")
Yes, I hadn't noticed that (not a good day to-day), but there are still error in the DLookUp ie extra [& "] inside the string after strWalls, srtRoof and strFloor.
should be
DLookup("[Construction Code]", "tblConstructionCodes", "[Walls]='" & strWalls & "' & " AND [Roof] = '" & strRoof & "' AND [Floor] = '" & strFloor & "' AND [Frame] = '" & strFrame & "'")
I believe !
MTB
Back again.
Sorry but that didn't work either. I am now getting A Compile error, Expect: =
I do appreicate your help, I thik I'll give it one more try & I think I'll give up for a while.
Viv
Bear with me. I have done this many times, but seem to have forgotten what I did. I think the only problem is that I should not have put quotes in the assignment of variable. Try the revision below:
Dim strWalls As String
Dim strRoof As String
Dim strFloor As String
Dim strFrame As String
' quotes removed from variable assignments below because variable type defined as text string
strWalls = Forms![frmQuotation]![frmSituations].Form![Walls]
strRoof = Forms![frmQuotation]![frmSituations].Form![Roof]
strFloor = Forms![frmQuotation]![frmSituations].Form![Floor]
strFrame = Forms![frmQuotation]![frmSituations].Form![Frame]
DLookup("[Construction Code]", "tblConstructionCodes", "[Walls]='" & strWalls & "' & " AND [Roof] = '" & strRoof & "' & " AND [Floor] = '" & strFloor & "' & " AND [Frame] = '" & strFrame & "'")
Viv,
Did you try my revised syntax above? In case it does not work the way you want, I have taken your original post and revised the syntax that you had...so try this as well: Hopefully one of the two (or both) will work.
DLookup("[Construction Code]", "tblConstructionCodes", "[Walls]='" & Forms![frmQuotation]![frmSituations].Form![Walls] & " and [Roof]= '" & Forms![frmQuotation]![frmSituations].Form![Roof] & " and [Floor]= '" & Forms![frmQuotation]![frmSituations].Form![Floor] & " and [Frame]= '" & Forms![frmQuotation]![frmSituations].Form![Frame] & "'")
Viv,
Did you try my revised syntax above? In case it does not work the way you want, I have taken your original post and revised the syntax that you had...so try this as well: Hopefully one of the two (or both) will work.
DLookup("[Construction Code]", "tblConstructionCodes", "[Walls]='" & Forms![frmQuotation]![frmSituations].Form![Walls] & " and [Roof]= '" & Forms![frmQuotation]![frmSituations].Form![Roof] & " and [Floor]= '" & Forms![frmQuotation]![frmSituations].Form![Floor] & " and [Frame]= '" & Forms![frmQuotation]![frmSituations].Form![Frame] & "'")
Still some missing apostrophies!
DLookUp("[Construction Code]", "tblConstructionCodes", "[Walls]='" & Forms![frmQuotation]![frmSituations].Form![Walls] & "' AND [Roof]= '" & Forms![frmQuotation]![frmSituations].Form![Roof] & "' AND [Floor]= '" & Forms![frmQuotation]![frmSituations].Form![Floor] & "' AND [Frame]= '" & Forms![frmQuotation]![frmSituations].Form![Frame] & "'")
BTW if this code is running in the form frmQuotation (ie. the form containing the subform)
then it could be simplified to -
With frmSituations.Form
-
DLookUp("[Construction Code]", "tblConstructionCodes", "[Walls]='" & .[Walls] & "' AND [Roof]= '" & .[Roof] & "' AND [Floor]= '" & .[Floor] & "' AND [Frame]= '" & .[Frame] & "'")
-
End With
??
MTB
MTB,
We have victory. IT WORKS!! Thanks so much for your help. I will be keeping this code for future reference. Thanks again.
Viv
Sign in to post your reply or Sign up for a free account.
Similar topics
by: KLAU |
last post by:
I have a field that retrieves information from an expression in a
query. I have used a DLookup function to get the calculated field
from the query. However, the relationship is 1-to-many so one...
|
by: Rich Bernat |
last post by:
We have vending machines which are serviced by emptying the money
inside the machine and placing it into numbered bags. Each bag is
numbered independently of the machines. Each machine has a...
|
by: ShyGuy |
last post by:
I have a table with 4 fields. Three are used for criteria.
I can get the DLookup to work with 1 criteria with the following but
can't get it to work with 2 or three.
NumofAppts = DLookup("",...
|
by: chris.thompson13 |
last post by:
I am having a problem setting the criteria part of the DLookup method
correctly and am consequently getting an error message.
I have a database of staff duties, part of which is a query (qryDaily)...
|
by: bjaj |
last post by:
Hi
How do I use a boolean criterian with the funktion DLookup ?
I know the syntax for strings, numeric and date as follows
For numerical values:
DLookup("FieldName" , "TableName" ,...
|
by: MSeda |
last post by:
I have a loop that is controlled by a Dlookup statement with two criteria. Both criteria fields are checkboxes. I have tried an assortment of quotation marks in the criteria section and cannot get...
|
by: JHNielson |
last post by:
I have a query that I'm trying to update with a dlookup with multiple criteria
This is the string:
EVNT_DT: DLookUp("","","( .EVNT_QTR=.) & (.=.)")
When i run it it says it can't find the...
|
by: BradHodge |
last post by:
Wouldn't ya know it. Posting a question on my first day of "ExpertDum" :)
How do you use DLookup with multiple criteria from the same table?
I have a report and I want a text box to display...
|
by: Denise |
last post by:
Front end is Access 2002, back end is linked Oracle tables.
My users need to describe things in feet and inches and want to use
the standard ' and " abbrevations.
On a testing form I go to a...
|
by: Constantine AI |
last post by:
Hi i am trying to get User input if data does not exist within a DLOOKUP table. I have gotten it to work for one record but not multiple, i have tried to incorporate my code into a loop procedure but...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
