Dlookup using Global variable

[surethom]

I have a query that looks up a table to get a cost.
Query:- QRY_recipe_ingredients
Fields:- RI_Ing_ID, RI_Resource_Code

Table:- TBL_Costset
Fields:- Ing_Resource_Code, C100, C101, C102, C103, C104


In Query (Qry_recipe_ingredients) I have a field "Costset1" that looks up TBL_Costset & gets the price from column C100 Costset1: DLookUp([c100],"tbl_CostSet",[ing_resource_code]=[ri_Resource_code]) This works.

I have a global variable that I want to use to change the column depending on which costs we want to use, I have put the global variable "selected_costset_no" in the dlookup "Costset2: DLookUp("[" & selected_costset_no() & "]","tbl_CostSet",[ing_resource_code]=[ri_Resource_code])" but only picks up the first item in the column i.e. 97

Hope someone can help.

 

[MarkK]

In a database the minimum addressable location is a row, so if you have 12 different values of the same type in a single row, you are hooped. The places you have field names like C100, C101, C102, each of those values should be in a related table where the C100, etc, is data. Each of those values needs to be in it's own record.

For lots more of this kind of information about structuring tables in a database, google 'normalization normalize database tables.'

 

[surethom]

I have finally worked out the formula.

CostKG: DLookUp(selected_costset_no(),"tbl_CostSet","[ing_resource_code]='" & [ri_Resource_code] & "'")
:banghead: