Query filter problem

[dim]

Hi,

I have a form “Tools List” based on a query “Tools List Query”
I need to filter more fields on the form using a combo box for each field.
As example to filter “Radius” field I’m using cbo_Radius and in the criteria query:
Is Null Or Like [Forms]![Tools List]![cbo_Radius] & "*"

The problem is when I filter using a specific Radius value, the results will include the null records too.
If I use Like [Forms]![Tools List]![cbo_Radius] & "*" the problem is that the null records will not appear when is nothing selected in the cbo_Radius

In other words I need something like:

Iif([Forms]![Tools List]![cbo_Radius] is Null, Is Null Or Like “*”, Forms]![Tools List]![cbo_Radius])

Can you help me please?

Thanks

 

[theDBguy]

Hi. Try using this:

[SIZE=3][FONT=Calibri][Forms]![Tools List]![cbo_Radius] [/FONT][/SIZE][SIZE=3][FONT=Calibri]Is Null Or Like [Forms]![Tools List]![cbo_Radius] & "*"

But if you're trying to filter a numeric field, you shouldn't be using the Like operator.
[/FONT][/SIZE]

 

[pbaldy]

Is this what you're after?

http://theaccessweb.com/queries/qry0001.htm

 

[dim]

Thank you theDBguy, but I tried [Forms]![Tools List]![cbo_Radius] Is Null Or [Forms]![Tools List]![cbo_Radius] & "*" and doesn't show any records.

Thank you pbaldy, I tried:
[Forms]![Tools List]![cbo_Radius] OR [Forms]![Tools List]![cbo_Radius] Is Null

and is working fine for Radius, but when I try to add a similar code for another field, doesn't work for the second. Ex:
[Forms]![Tools List]![cbo_FlutQty] OR [Forms]![Tools List]![cbo_FlutQty] is Null

Any idea?

 

[pbaldy]

It should work for multiple fields/controls, though design view will make more than a couple of fields look pretty odd. For more than a field or two I tend to create SQL dynamically, as demonstrated in the sample db here:

http://www.baldyweb.com/BuildSQL.htm

 

[dim]

I tried for more fields, but I can't figure out a good solution.
So applied to one field is working, but not for the others fields.
[Forms]![Tools List]![cbo_Radius] OR [Forms]![Tools List]![cbo_Radius] Is Null

Any others ideas?

 

[pbaldy]

Other than the one I already suggested, no.