Hello!

I need a little of help please:

In a report I use the Dlookup function matching 1 argument (and it works well):

=DLookUp("VALUE2RETURN","SOURCE","MATCH = " & Nz([MATCH],0))

Now, I need to retrieve a single value, matching 3 different columns (like a multiple Vlookup in Excel)

So, the code so far, goes like this (trying to match 2 criteria):

=DLookUp("VALUE2RETURN","SOURCE","MATCH1 = " & Nz([MATCH2],0) And "VALUE2RETURN", "SOURCE", "MATCH2 = " & Nz([MATCH2],0))

But is doesn't work...in excel is easier, because you can create a column with "&" and use vlookup then.

Your help would be much appreciated!

Hi

You need to include the word AND within the quotes and remove the second references to the field and table



assuming numerical data types

Thanks a lot for your help!

Its works great, now, how do I change the code if there are 2 strings and 1 numerical??

I think that I'm missing the part that handles empty data...and it doesnt work, neither...

=DLookUp("VALUE2RETURN","String1= 'String1'" And "String2= 'String2'"" And Numerical = " & Nz([Numerical],0))

Could you help me with this issue, please?

Thanks again!!

Hi

If your strings are literal ones (ie hard coded) then it would be



or if the strings were variables then it would be



I've used line continuation characters to make it easier to read, but you don't need them in the expression as you'd use it in the control source

I though that the data from the tables was hard-data, but the code wasn't working. At last I tried with the code for variables provided by you, and it worked great.

I really appreciate your help Alan G, thanks for your support!

You're very welcome - glad you got it working