Lookup Query

[bellis]

I learned not to do calculated fields in a table, so I'm trying to build the Query. I need a lookup based on 4 attributes. A number value is designated per row based on the 4 attributes. I need to get that number.

Below are my two expressions. The first works, the second doesn't.

Expr1: [Data1] & " - " & [Data2] & " - " & [Data3] & " - " & [Data4]

Expr2: DLookUp("[ReferenceSheet]![Number]","[ReferenceSheet]","[ReferenceSheet]![ComparisonConcatenation]=[Expr1]")

On my form I got that formula to work, but it doesn't work in the Query. It has something to do with the Criteria portion because I'm getting a Data Type Mismatch.

Is there a better way to do this? I'm still new and largely excel minded

Thanks in advance!

 

[boblarson]

You can't refer to a calculated field from another calculated field in the same query. You would have to change to:

Expr2: Nz(DLookUp("[Number]","[ReferenceSheet]","[ComparisonConcatenation]=" & Chr(34) & [Data1] & " - " & [Data2] & " - " & [Data3] & " - " & [Data4] & Chr(34)),0)

And you need quotes ( I used the Chr(34) for quotes) around the concatenated data and the fields need to be outside of the DLookup Quote. And it is best to use the NZ function so you don't get any "invalid use of null" errors. And you don't need to use the table reference in either the first part of the DLookup (for the field to return) nor for the criteria unless you have the same exact name of a field in more than one table in the query are using the DLookup on (if using a query).