QUERY Criteria dependend on ISNULL or NOt

  • Thread starter Thread starter BlueWolverine
  • Start date Start date
B

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
 
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.
 
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
'====================================================
 
Back
Top