Using a Frm to establish Qry Criteria

[agfon]

I have a form with several text and combo boxes. It it set up to allow the user to input criteria and then run a query.

In the criteria area of the query, the criteria of each column is set in the following fashion:

[blue][forms]![my_frm]![my_textbox][/blue]

The problem I'm experiencing is when the textbox is left blank (to pull all records). I've tried using the Nz() function without success. Here's an example:

[blue]Nz([forms]![my_frm]![my_textbox],Is Null or Is Not Null)[/blue]

It seems the only way around this is using the IIF() function; however, this really slow the query down. Can anyone point me in a direction that will help?

Thanks.

-agfon

 

[PHV]

=[Forms]![my_frm]![my_textbox] OR [Forms]![my_frm]![my_textbox] Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[agfon]

Thanks PHV. That seems to work. I'm trying it out now to see if there is any performance improvements.

-agfon

 

[agfon]

PHV,

The only drawback is that it really increases the size of the SQL code when you have multiple columns utilizing this method. My query has around ten columns. When I use this method, the query fires fine. However, I cannot enter the query in design view afterwards. Access basically locks up for around 5 minutes and then provides me a message stating that there are not enough resources to open the query in design view.

-agfon

 

[PHV]

And what about the SQL view pane ?
SELECT ...
FROM ...
WHERE ([my_field1]=[Forms]![my_frm]![my_textbox1] OR [Forms]![my_frm]![my_textbox1] Is Null)
AND ([my_field2]=[Forms]![my_frm]![my_textbox2] OR [Forms]![my_frm]![my_textbox2] Is Null)
AND ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[agfon]

PHV,

My SQL view pane is similar to your example. My guess is that I have too many fields that I'm trying to limit with this method.

-agfon

 

[PHV]

Is the query running ?
If yes, why going in design view ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[agfon]

PHV,

The query is in the process of being adjusted to account for additional requirements.

-agfon

 

[PHV]

Simply adjust in the SQL view pane !

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886