Query on other queries inc. IS NULL

[trebormint]

I have a form which a user can select upto 3 different options to search the main database.

The main table has:
RVA Date
Council
Introducer
PS NO
Period
Asset Description
Current Cost
SSAP21
Position

The three fields the user can search on is:
Council
PS No.
Asset Description

I have created 3 individual queries to find the records for each of the above, as the other two choices could be left blank.

Not sure if one super query can be done to show the records based on the user input (as I say one or two choices could be left blank).

So far I have managed to get a main query (based on the 3 other queries) to work on all choices made by the user except Asset Description with the other two left blank.

I have made relationships between the three queries on the main query. Linking Council,PS No. & Asset Description to each other.

Almost there, just need the main query to work on the user selecting Asset Description only.....

 

[Dennisk]

one query will do this.
In the criteria cell in the QBE grid enter

=Forms!YourFormname!YourControlName OR Forms!YourFormname!YourControlName is null

Access will restructure this for you by creating an extra column for the null with a corresponding OR.

Note the use of is null , this is used in queries whilst isNULL(control) is used in VBA code.

 

[Brianwarnock]

This Link explains in more detail what Dennisk is illustrating.

Brian