Help with DLOOKUP with 2 criterias. 1

[robles74]

Hi everyone.

I need your help to solve an issue with a DLOOKUP with multiple criterias.

I already have solved the Dlookup when it "looks" for one criteria, I mean when a certain number is entered in a text box in a form, the dlookup see this number and returns ALL the information related to this record/row.

The dlookup "search" the "User" (text) inside the "RTS_Survey" table based in the information typed/entered in the field "Unique Survey_ID" (text) who is inside the form "Dental_Survey" and the working sintax is as follows:

=DLookUp("[user]","RTS_Survey","[Unique Survey-ID] = '" & [Forms]![Dental_Survey]![Unique Survey-ID] & "'")

Now I need to "combine" with 2 (two) conditions/criteria to get the "User" in another database. I need this:

When the "Full Name" (text) and the "ZIP" (text with the zip code input mask) match in one record, the dlookup return the user information, the same for the other fields such, Address, City, State, etc.

Until now I have this:
=DLookUp("[user]","Address","[Full Name] And [ZIP] = '" & [Forms]![Address]![Full name] And [Zip] & "'")

But anything I put/enter in the "Full Name" and "ZIP" only returns the information contained in the first record in the table.

Any help will be really appreciated.

Thanks in advance.

 

[dhookom]

Assuming both fields are text, try:

=DLookUp("[user]","Address","[Full Name] = '" & [Forms]![Address]![Full name] & "' And [ZIP] = '" &  [Zip] & "'")

Duane
Hook'D on Access
MS Access MVP

 

[robles74]

Thank you dhookom, you are the man.

It works, I'm still reviewing the sintax to clearly understand the reason.

Looks like I was "near" to the solution but I need to know what exactly was wrong with my sintax, I need to know to replicate this fix if I need to do.

Again thank you very much.

 

[dhookom]

To get your head around it, build a query with the criteria and then look at the SQL view.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

... or you may 'cheat' and see it this way:

Dim strLookItUp As String[green]
'Where [/green]
strLookItUp = "[Full Name] = '" & [Forms]![Address]![Full name] & "' And [ZIP] = '" & [Zip] & "'"

Debug.Print strLookItUp

=DLookUp("[user]","Address",strLookItUp)

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[dhookom]

Andy,
I'm not sure robles74 was using any code/vba since it looked like the expression might just be a control source.

I'm also not sure about the use of [highlight #FCE94F][Forms]![Address]![Full name][/highlight] and just [highlight #FCE94F][Zip][/highlight] without the [highlight #FCE94F]Forms!Address[/highlight].

Duane
Hook'D on Access
MS Access MVP