Relation between tables in query and criteria

  • Thread starter Thread starter a
  • Start date Start date
A

a

Access 2007

I have query

I add 3 tables and make join between them

My question is:

I'm using this expression

forms!formname!Controlname or forms!formname!Controlname is null

with 2 field

explanation:

the fields for the criteria is from different tables

the problem is :

to get the result of query must fill 2 control

how can solve this is the problem
 
That expression in the Criteria row in query design won't work.
The second part of the expression, i.e.:
forms!formname!Controlname is null
results in -1 (true) if the control is null, or 0 (false) if the control has
a value. You've asked Access to compare that result to your field, so it
will only match if the field actually does contain -1 or 0 respectively.

Instead, switch the query to SQL View, and modify the WHERE clause.
Currently it will say something like:
WHERE (SomeTable.SomeField) = forms!formname!Controlname
OR (SomeTable.SomeField) = (forms!formname!Controlname is null)

What you need is:
WHERE ((forms!formname!Controlname is null)
OR (SomeTable.SomeField = forms!formname!Controlname))

If you have several of these fields, the SQL statement gets unwieldy and
inefficent. Here's an alternative approach:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
 
Back
Top