DLookup with multipul criteria

Trying to get this multicriteria dlookup  to work but I keep getting syntax errors..... any assistance is appreciated.

Thank you.

sAccountNo = DLookup("ActNo", "tOutLab", "Lab = " & "'" & Me.OutLab & "'" & " AND " & "'" & "Office = " & "'" & Me.Office & "'")
thandelAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Kelvin SparksConnect With a Mentor Commented:
I find using single quote around text values is easier that endless double quotes, so I'd suggest
sAccountNo = DLookup("ActNo", "tOutLab", "Lab = '" &Me.OutLab & "' AND Office = '" & Me.Office & "'")


Kelvin
0
 
chaauCommented:
Can you advise what data types are the columns tOutLab and Office in your ActNo table?
Can you advise what controls are Me.OutLab and Me.Office (most likely text boxes, but please double check)
0
 
thandelAuthor Commented:
Yes the tOutlab is a string, Office is also a string, Me.Outlab and Me.office are combo boxes.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
thandelAuthor Commented:
Thanks but I'm getting a run time error of "you have cancelled the previous operation"
0
 
chaauCommented:
Depending on how Combo Boxes are created either first or second will work:
sAccountNo = DLookup("ActNo", "tOutLab", "Lab = '" & Me.OutLab.Text & "' AND Office = '" & Me.Office.Text & "'") 

Open in new window

sAccountNo = DLookup("ActNo", "tOutLab", "Lab = '" & Me.OutLab.Value & "' AND Office = '" & Me.Office.Value & "'") 

Open in new window

Explanation: You can create Combo Boxes on the form that display text for the items, but the values for the items will have different codes. In this case the actual value that the tables need to be compared with need to be tested against "Value" property. Most of the time, however, you need to test against "Text" property
0
 
thandelAuthor Commented:
Sorry neither is working... I don't think I need that as the following is working:

sAccountNo = DLookup("ActNo", "tOutLab", "Lab = '" & Me.OutLab & "'")

It just the multi-criteria that I can't figure out.
0
 
Kelvin SparksCommented:
Chances are the combo uses an ID and displays the text. Try
sAccountNo = DLookup("ActNo", "tOutLab", "Lab = '" & Me.OutLab.Column1 & "' AND Office = '" & Me.Office.Column1 & "'")
0
 
thandelAuthor Commented:
Wait sorry you had it above, I had my table mixed.... sorry about that.
0
All Courses

From novice to tech pro — start learning today.