DLookup in Query

cat001969

cat001969
Local time
Today, 02:28
Joined
Jun 19, 2005
Messages
17
I am doing a Dlookup in a query based on the value of one table to look up the value of another field in a table based on two other fields in the second table.

Although my code is presently returning data it is not correct data. Any suggestions?

Test: IIf(IsNull([Attend_Prov_MMIS_Id_W039]) And Not IsNull([Attend_Prov_Lic_Num_3003_2]) And Not IsNull([Attend_Prov_Prof_Code_2165_5]),DLookUp("[Provider_Name]","tblAllProviders","tblAllProviders.[Provider_Lice_Num]='" & [Attend_Prov_Lic_Num_3003_2] & "'&" And "& " & "tblAllProviders.[Provider_Code]='" & [Attend_Prov_Prof_Code_2165_5] & "'"))
 
& is the concatenation operator. It combines two strings into one. It is not a substitute for the relational operator AND. AND is used to connect two logical expressions. + is the addition operator. It can substitute for the & when one or both of the fields being concatenated are strings but it operates differently with nulls. Review help before using the + as a concatenation operator to avoid unexpected results.

In any event, DLookup() (or other domain functions) should never be used in queries. Use a left join to the lookup table to obtain the column you want.
 
The join would not work for what I need to do. I have 5 sets of 3 fields, if the first field is not null then it needs to pull the name of the matching provider, if it is null then it has to look at the next two fields and pull the name of the provider as well. It then must return only one name for the provider. It has to do this 5 separate times pulling up to 5 different provider names for one record.
 
Last edited:

Users who are viewing this thread

Back
Top Bottom