Filtered and unfiltered Query 2

[dainamae]

Hello,

I have a query that is filtering data based on a field on a form that may or may not contain data selected by the user. This query selects the data correctly when there is data in this field, however, I cannot figure out how it to select it correctly when the field is empty. Can this parameter be dynamically created based on if the field is empty or not? Right now I have the [Forms]![FormName]![Field] criteria in the query, but this only works when there is data in the field. Any suggestions?

Thanks in advance!

Daina

 

[ghubbell]

Hi Daina,
Give this a go:

Like (IIF(IsNull([Forms]![FormName]![Field]),"*",[Forms]![FormName]![Field] ))

Means if there's nothing in the field, * , otherwise the value in the field! Hope that'll do it. Gord
ghubbell@total.net

 

[dainamae]

Thank you thank you!! I spent an hour last night trying to get this to work, and the only thing that was different is that you had Like in front the entire string and I had it only in front of the "*"!

Thanks again!

 

[JerryDennison]

Actually, there's an even easier way to do this without have to use IIF and IsNull:

Like Forms!FormName!ControlName & "*"

 

[ghubbell]

Jerry! What if Daina wants the criteria to give results that equal 1. Not 10,11,12...? Gord
ghubbell@total.net

 

[JerryDennison]

True, I should've put a disclaimer in that you wouldn't want to use this with numeric answers. But it is highly unusual to set criteria for numbers(as a rule, unless of course the number is a primary key or something and then you're usually passing the entire pk). So I figured I was safe.

 

[toyshoppe]

Could I take this thread a little farther? I've had the same problem putting a variable into a LIKE criteria.

Your solutions work great... except for a query field that is a primary numberic key.

It appears that a LIKE * does not work on numberic fields, is this true?

Jay