Using a table field as criteria.

[Nick]

I have a single field table called tbLocal and the field
is called Local. I would like to use all the records in
tbLocal as criteria in a query when the combo box control
in a form is set to Local. This should return only the
records in the query that match tbLocal.
If I set the combo box control to All, the query ignores
the tbLocal and returns all records.

I hope this makes sense. If you can help please keep it
simple as I’m no expert.

Regards
Nick

 

[Michel Walsh]

Hi,


SELECT a.*
FROM queryA As a LEFT JOIN Local AS b
ON a.FieldName = b.FieldName
WHERE iif( FORMS!FormNameHere!Local = 'Local', Not a.FieldName Is Null, -1 )


where queryA is the ... thing... you retrieve the data from ( "... should
return only the records in the query that...") and FieldName is the field
used to detect a match. FormNameHere is assumed to be the form name, Local
its control having the value 'Local', or something else. If it is a check
box rather than an control with a string, then use something like:

WHERE iif( FORMS!FormNameHere!Local , Not a.FieldName Is Null, -1 )




Hoping it may help,
Vanderghast, Access MVP