QUERY criteria dependent on Is null do A, not is null do 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

 

[Bob Barrows [MVP]]

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)

WHERE (Fieldname = [SearchCriteria] Or [SearchCriteria] is Null)
AND (Fieldname2=[SearchCrit2] Or [SearchCrit2] Is Null) ...

Your users may not appreciate the performance ...

 

[BlueWolverine]

I take that to mean that's going to be really slow. Is there a better way?

I want to be able to return the list of things meeting certain criteria. (I
will be doing LIKE searches in the sense of finding "All records with [Field]
containing [Search Crit])

The example I asked about is for the direct 1:1 matches but I will be doing
something similar for contains searches.


--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!

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)

WHERE (Fieldname = [SearchCriteria] Or [SearchCriteria] is Null)
AND (Fieldname2=[SearchCrit2] Or [SearchCrit2] Is Null) ...

Your users may not appreciate the performance ...

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

 

[Bob Barrows [MVP]]

I take that to mean that's going to be really slow. Is there a
better way?

Much as I hate to use dynamic sql, I have to think that it will be better
to build the sql strings in VBA.

 

[Bob Barrows [MVP]]

I take that to mean that's going to be really slow. Is there a
better way?

But test it to make sure the performance is bad enough to justify using
dynamic sql.

 

[BlueWolverine]

It could just be the number of records I have (Not all that many) but the
performance seems fine.
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!

 

[Bob Barrows [MVP]]

That's why I urged you to test it.

It could just be the number of records I have (Not all that many) but
the performance seems fine.