asked on

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 & "'")

chaau

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)

thandel

ASKER

Yes the tOutlab is a string, Office is also a string, Me.Outlab and Me.office are combo boxes.

ASKER CERTIFIED SOLUTION

Kelvin Sparks

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

thandel

ASKER

Thanks but I'm getting a run time error of "you have cancelled the previous operation"

chaau

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

thandel

ASKER

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.

Kelvin Sparks

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 & "'")

thandel

ASKER

Wait sorry you had it above, I had my table mixed.... sorry about that.