Query Dlookup different fields

[newwales]

I have a Recipe with a price file C97, every couple of months we create a new price field C98, C99,C100 etc… Within the recipe form I have a field that I enter which price list I want i.e C98.



Currently I have the following in the query
Resource_Code TotalCost2
158165 0.156
105179 7.5

Below is the folmula in TotalCost2, which looks up filed C98 within Costset Table where the Resource_Codes are the same.

Totalcost2: DLookUp([C98],"CostSet",[Resource_Code])

What I would like is on the Form to enter C97 or C98 or C99 & for the Query Dlookup to lookup the C97/C98/C99 field automatically.


Hope you can help.

[apr pillai]

If I understood you correctly you can use the following expression in your Query Column for bringing in the Value from Price File:

TotalCost2: DLookUp([Forms]![FormName]![txtBoxName],"PriceFileName","lng_Resource_Code = " & [Resource_Code])

When you enter C97 or C98 or C99 in the Text Box on the Form the Query will lookup that field on the Price File to return the Cost for the corresponding Resource_Code in the Query.