DLookup Using Multiple Criteria

[tammy23]

Access 2000, Windows XP

I need to lookup an entry in WellLogDetails based on two criteria in Report2 and all three of the following codes return errors....any suggestions?

'a = DLookup("[FileLoc]", "WellLogDetails", "[Well]=Reports!Report2![Well]" And "[ID]=Reports!Report2![ID]")

'a = DLookup("[FileLoc]", "WellLogDetails", "[Well]='" & Reports!Report2![Well] & "' AND [ID]= '" & Reports!Report2![ID] & "'")

a = DLookup("[FileLoc]", "WellLogDetails", "[Well]=[Reports]![Report2]![Well] and [ID]=[Reports]![Report2]![ID]")

 

[Remou]

How about:

a = DLookup("[FileLoc]", "WellLogDetails", "[Well]=Reports!Report2![Well] And [ID]=Reports!Report2![ID]")

http://lessthandot.com

 

[dhookom]

Is this code or a control source of a text box?
Are both Well and ID bound to controls in your report?
If this is code, is a a string variable?
Are both Well and ID numeric?

Assuming both fields are numeric...
Have you considered a text box with a control source of:

=DLookup("[FileLoc]", "WellLogDetails", "[Well]=" & [Well] & " And [ID]=" & [ID])

If this is in code (for some reason) make sure the fields are bound to controls and try:

a=DLookup("[FileLoc]", "WellLogDetails", "[Well]=" & Me.[Well] & " And [ID]=" & Me.[ID])

Duane
Hook'D on Access
MS Access MVP

 

[tammy23]

Thanks for the the help

ID is numeric, Well is not. Remou's code worked great:

a = DLookup("[FileLoc]", "WellLogDetails", "[Well]=Reports!Report2![Well] And [ID]=Reports!Report2![ID]")

Thanks!