DLookUp in criteria

[JIM.H.]

Hello,
I checked the result of this command: DLookUp
("[myPar]", "[myTable]") is "'AT','BT','CT'" and I need to
use this formula in the criteria of the query I define
as "IN (DLookUp("[myPar]", "[myTable]"))". But it does not
seem working in the query. Am I allowed to do this? If
not, how can I dynamically change the criteria of a query?
Thanks,
Jim.

 

[John Vinson]

Hello,
I checked the result of this command: DLookUp
("[myPar]", "[myTable]") is "'AT','BT','CT'" and I need to
use this formula in the criteria of the query I define
as "IN (DLookUp("[myPar]", "[myTable]"))". But it does not
seem working in the query. Am I allowed to do this?

No. Storing three values in a single field as you're doing is never
appropriate!

If not, how can I dynamically change the criteria of a query?

A couple of ways -

WHERE InStr([myfield], DLookUp("[MyPar]", "MyTable")) > 0

will do a sort of "inverted" search, looking for the value of the
field somewhere within your parameter; or, by using the value from
MyPar in VBA code to construct the SQL text of a query.