DLookup in a query expression

[trandel]

Hi,

I have a DLookup expression that is working in a Control on a form :

=DLookUp("[Class] ","Class","([Forms]![Orders_Crosstab]![Indcat] = [Clind]) AND ([Forms]![Orders_Crosstab]![2005] Between [From] AND [To])")

I simplified it for test purposes as an expression in a query:

Expr1: DLookUp("[Class] ","Class","([Indcat] = [Clind])")

This gave an error - MS Access cant find the name [Indcat] in the expression.

After some R&D on the web I found a solution that works:

Expr1: DLookUp("[Class] ","Class","([Clind]=" & [Indcat])

[Indcat] and [2005] are part of the recordset where [Clind], [From] and [To] are values in the Table 'Class'

MY CHALLENGE:

I cannot find the correct way to add the extra selection criteria to the query expression (as per the form expression above) that checks the value [2005] is between the [From] and [To] values in the table. Have tried a number of combinations without success.

Any help, suggestions and/or guidance very welcome.

Regards

Tony Randell

 

[Dennisk]

Usually there is no need to use DLOOKUP in a query, just link the Class table into your query.

 

[trandel]

Hi Dennisk

Just realised just how right you are! I was looking for complex solutions to simple problems.

Thanks and Regards

Tony Randell