Dlookup multiple criteria - help needed

[ppataki]

Dear All,

I have a query that uses a dlookup function to fetch the previous record
and it works fine

DLookUp("ejfelig";"qry_report";"datum=#" & [datum]-1 & "#")

I have another field: "Namefield" in the table that currently has only one value (eg. Peter)
My problem is that if I add more values (eg. George) the above dlookup gets crazy...I think somehow I would need to set dlookup to lookup only within one name at a time (but of course I would like to display all the names)

Could you please advise?
Many thanks in advance

 

[Guus2005]

Instead of dlookup-ing a name you should join the table containing the names.
This has several advantages. Your query is a lot faster, not using a dlookup (or any domain function for that matter)!
Your query should look like this:

Select DLookUp("ejfelig";"qry_report";"datum=#" & [datum]-1 & "#") AS PrevRecord, 
   table1.*, table2.NameField from table1 inner join table2 
   on table1.id = table2.id

HTH