Parameter as Null to Return All? 1

[scarlo]

I'm drawing values for my parameter query from a form with drop down boxes.

What I want is for the query value to be drawn from the drop-down box, or if the drop down is left as blank then "*" wildcard for all data. There will be many drop-down boxes that relate to different parameters, so I need them to return all when blank.

I can draw the data fine as:
Criteria: [Forms]![Sort]![Contact]

I've tried:
Forms![Sort]![Contact] Or Forms![Sort]![Contact] Is Null

But this only gives me something if I have a choice from the drop-down- it gives nothing when there is no choice or if any of the drop-downs are blank even.

Also tried:
Like "*" & [Forms]![Sort]![Criteria] & "*"

But this returns all results, so it doesn't really sort at all.

I think I need a combination of the two...

How can do I do something like the following ?:
[Forms]![Sort]![Contact] or if Is Null, then like "*"

I think something like that, if not conflicting with itself, would be what I want.

Any suggestions or ideas?

 

[PHV]

Perhaps this:
Like Nz([Forms]![Sort]![Criteria], "*")
Or this:
= [Forms]![Sort]![Contact] Or Trim([Forms]![Sort]![Contact] & "") = ""

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

 

[scarlo]

Like NZ([Forms]![Sort]![Contact], "*")
as the criteria for Contact and
Like NZ([Forms]![Sort]![Contact], "*")

I get the appropriate table return if I enter form values for both, but if either is missing then it returns an empty table...

 

[scarlo]

However, the second choice of
= [Forms]![Sort]![Contact] Or Trim([Forms]![Sort]![Contact] & "") = ""

Does work! Thanks, I been trying to figure this pickle out for awhile now, this is a HUGE help

 

[scarlo]

Ok, the only problem is that when I
= [Forms]![Sort]![Contact] Or Trim([Forms]![Sort]![Contact] & "") = ""
for 8 parameters, the "query is too complex" error pops up.