Table with Lookup Field in Query (1 Viewer)

[jaydwest]

I have a table with a Lookup field. The field is a text field and the lookup table for the field is like:

PK Code Value
-- ----- -------
1 AA AA Value
2 BB BB Value
3 CC CC Value

THe code is displayed in the table, but the field is bound to column 1.

I then created a query to display the description Value (AA value ....) by joining this table to the Lookup Fields underlying table.

Because the field in the table is a string I got a type mismatch on the join, so I cast the value of the lookupfield using

Clng(nz(LookupFIeld, 0))

Now I get the value AA Value, BB Value, CC Value displayed as wanted. Looks great, except I get the message "Data type mismatch in criteria expression." When I click OK the values displayed disappear.

Any suggestions.

 

[Trevor G]

Can you upload a copy of the database so someone can see the query and help sort this for you.