DLookup with multiple criteria

[RunTime91]

Greetings Experts ~

I'm trying to display an EmpID in one textbox using DLookup and two criteria (the employee's first & last name entered into two other textboxes)


My first attempt below returns only the first EmpID in the EmpID field regardless of what names are typed into the first & last name textboxes.

=IIf(DLookUp("FirstName","Employee","FirstName = '" & [Forms]![Request]![TxtSrchFirstName] & "'")
And (DLookUp("LastName","Employee","LastName = '" & [Forms]![Request]![TxtSrchLastName] & "'")),[EmpID],"")


Below is my second attempt and it returns only a #Name? error

=(DLookUp("EmpID","Employee","[FirstName]= " & [Me].[TxtSrchFirstName] & " And [LastName] = " & [Me].[TxtSrchLastName] & ""))




As always, your help is greatly appreciated ~

[tonez90]

Hi,
try something like this (I assume the txtSrch.. are strings) - I use something similar
See if this works for you

Dim varx As Variant
varx = DLookup("EmpID", "[Employee]","([FirstName] LIKE " &me!TxtSrchFirstName & ") And ([LastName] LIKE " & me!TxtSrchLastName & ")")

[RunTime91]

Update ~

After reading the 'similar posts' which accompany all new posts (Awesome feature btw!) I was able to solve with the following:

=(DLookUp("EmpID","Employee","[FirstName]= '" & [Forms]![Request]![TxtSrchFirstName] & "' And [LastName] = '" & [Forms]![Request]![TxtSrchLastName] & "'"))


While this appears to be working as expected if anyone sees any potential issues please advise.

Also, and again, while this appears to be working I do not know why it works. I am very familiar with Excel formulas & functions however the formula's & functions used in Access bewilder me. Is there any place I can go to learn about the syntax of Access formula's & functions. Specifically the use of quotations; single quotations; brackets; ampersands; etc. Any tutorial on the how, when, why they are used would be great!

Again, as always

Thank You ~

[RunTime91]

Tonez90 ~

Thank you for chiming in ~ I look forward to using your solution if just to add another method in my arsenal ~

Thank You again very much ~

[tonez90]

Your welcome. In case you are not familiar with referencing
[Forms]![Request]![TxtSrchFirstName] is the form Request and the field TxtSrchFirstName this can be changed to Me![TxtSrchFirstName] as long as you dont use sub-forms (form within a form). If this is the case do a search and there is some great refernce material.

I tend to use the LIKE as you are comparing strings. If it was a number I would use the = sign. You can also use * (wildcard) if you want to use a patter (Example: Like *" & me![TxtSrchFirstName] & "*").
I always find that using a variable for the dlookup is better (save heart ache and then convert to string using the cstring function.

Anyway good luck with you project.

If you do a search on dlookup you will find a lot written including tutorials.