DLookup on calculated feild

[prs312]

I'm having trouble linking (via the DLookup function) a calculated field from one table to a lookup table in another table:


-tblEQ5D has a calculated column EQ5D_HealthState that is the concatenation of the 5 input fields (e.g. 3, 2, 3, 3, 3, becomes 32333)
-this value relates to a “US_Utility” value found in tblEQ5DLookup (e.g. in the lookup table value 32333 correlated to a US_Utility value of 0.616)
- I want to Add a column in a query (qryEQ5D) that shows the Utility value for the EQ5D_HealthState value
-I tried using the following but havent been able to get this to work: Utility: DLookUp("US_Utility","tblEQ5DLookup"," HealthState_ValueSet = [EQ5D_HealthState] ")

- I also tried relating the two fields in the database relationships but get an error stating that calculated fields can't be apart of relationships :/

See below image for clarification:

[June7]

Part of the problem is the dynamic parameter is within quote marks. Also, different data types.

Try:

DLookUp("US_Utility", "tblEQ5DLookup", "HealthState_ValueSet =" & Val([EQ5D_HealthState]))


Make HealthState_ValueSet a text field and then instead of DLookup, try joining the two tables in a query.

[prs312]

That worked! thanks a lot June7