DLookup in Query

[cat001969]

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] & "'"))

 

[cat001969]

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.