Dlookup in calculated control

[trandel]

Hi,

I am trying to create a calculated field in a query which uses another value in the query to lookup a table. I get an error running the query :

"Microsoft Access can't find the name 'Indcat' you entered in the expression."

The expression I am using is as follows:

Expr1: DLookUp("[Class]","Class","([Indcat] = [Clind]) AND ([2007] Between [From] AND [To])")

Indcat and 2007 are valid field names in the query.

I used this Dlookup in a form and it worked fine (with Form! etc references to Indcat and 2007)

Mystified

Tony Randell

 

[Dennisk]

Hint: there is no need to use dlookup in a query. just link the tables.
Also your Between expression is incorrect as [2007] is not a valid column name as Access names cannot start with a number.

 

[trandel]

I cant link the tables.

The Dlookup is to find the appropriate class for the crosstab calculated total of Sales per client. There are 4 classes which refer to different levels of total sales value depending on Indcat. e.g Class A = between 100000 and 550000 for Indcat 1 but is between 15000 and 35000 for Indcat 2 etc etc.

Once allocated I want produce a report in Client within Class sequence.

 

[trandel]

Hi Dennisk

Took me some time to see it but you are absolutely right - link the table and use the normal criteria to select the correct entry.

I was looking for a complex solution to a simple problem as usual.

Thanks

Tony Randell