By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,998 Members | 2,921 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,998 IT Pros & Developers. It's quick & easy.

Dlookup with multiple criteria

P: 18
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] & "'")
Jul 20 '07 #1
Share this Question
Share on Google+
11 Replies


puppydogbuddy
Expert 100+
P: 1,923
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 & "'")
Jul 20 '07 #2

P: 18
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 & "'")
Jul 22 '07 #3

puppydogbuddy
Expert 100+
P: 1,923
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 & "'")

--------------------------------------------------------------------------------
Jul 23 '07 #4

P: 18
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
Jul 24 '07 #5

Expert 100+
P: 634
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
Jul 24 '07 #6

puppydogbuddy
Expert 100+
P: 1,923
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 & "'")
Jul 24 '07 #7

Expert 100+
P: 634
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
Jul 24 '07 #8

P: 18
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
Jul 25 '07 #9

puppydogbuddy
Expert 100+
P: 1,923
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] & "'")
Jul 25 '07 #10

Expert 100+
P: 634
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
Expand|Select|Wrap|Line Numbers
  1. With frmSituations.Form
  2.     DLookUp("[Construction Code]", "tblConstructionCodes", "[Walls]='" & .[Walls] & "' AND [Roof]= '" & .[Roof] & "' AND [Floor]= '" & .[Floor] & "' AND [Frame]= '" & .[Frame] & "'")
  3. End With
??


MTB
Jul 25 '07 #11

P: 18
MTB,
We have victory. IT WORKS!! Thanks so much for your help. I will be keeping this code for future reference. Thanks again.
Viv
Jul 26 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.