QUERY Criteria dependend on ISNULL or NOt

[BlueWolverine]

Hello
Access 03 on XP PRO

I have a search panel I am writing and I want to set up ONE query that will
be ready to search for any combination of restrictions. So I want the
criteria to function in this manner:

If(ISNULL(Search_Criteria), then Return all Values, else restrict to only
Search_Criteria)


How can I do this in SQL or does this have to be done with VBA on the button?

Also, I have the interntion of setting up a crude auto-filter, like the pull
downs in EXCEL.

I have five boxes you can typ etext into and 4 check boxes. The idea is to
search wether or field contains the txt in the boxes or not. The check boxes
indicate AND vs OR. I know that a query can easily produce AND & OR but can
it be done within the query or does it have to be vba that does it?

Thank you

 

[BlueWolverine]

My best guess for the VBA approach is to edit the STRSQL of the SQL
statement, using if's to determine where criteria go. I really don't like
this. But I fear it may be the only way.

I want other ideas if possible.

 

[John Spencer]

If you are displaying the data on a form, you can use the filter
property of the form to handle this and you won't need to modify the
base query.

If you are printing the data in a report, you can use the where argument
to pass the additional criteria from the form using

DoCmd.OpenReport "ReportName",,,"FieldA = 3 and FieldB ='Jake'"

You will need to build the filter or the where string using VBA.

I think Allen Browne may have a good example of this on his site.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================