Combo boxes and filtering

[paulhorn]

I have a form with 3 unbound combo boxes to allow the user to selct the search desired. Selection in the first box filters the selection available in the second, and the same for the second to the third combo. The selections form the criteria for a query, and the results come out on a subsequent form. This works really well if all 3 combos are used, but I cannot find a way make this run successfully if a selection is made only in combo box one (or combo's one and two), so that the search is broadened out.

Can anyone advise me if this is achievable?

Thanks

 

[Norie]

Are you using code to create the query or are the combobox values being used as parameters in a query?

 

[paulhorn]

Hi Norie,
Unfortunately, not code (don't yet have a good grasp!). THe combo's get their pull down lists from DISTINCT queries against the relevant fields of a table (combo's are unbound), and the selections made are entered into the criteria lines of the requisite fields of a query (in the form of Forms![FormName]![FieldName]). The query feeds the results to another form.

Does this help, or do I need to be more explicit?

Thanks

 

[Norie]

I don't know if it will work but you could try using Iif formulas in the criteria.

eg

Iif(Forms![FormName]![FieldName]="", "*", Forms![FormName]![FieldName]="")

That is if the field is not empty then use it otherwise use *.

EDIT

Also take a look at the Nz function.

 

[paulhorn]

Thanks for the pointer Norie,
I'll give it a try tonite

 

[paulhorn]

Hi Norie,
No joy I am afraid. Tried all sorts of variations of IIf statements (which will incidentally work if text string passed is anything but the asterisk "*").
I proved the point by creating text boxes on the search form to show what was being passed from the form to the query (and even used these text boxes as the source for the query).

It seems like the asterisk character sent by the form is not the same as that received by the query (*, "*", '*' , Like *, Like "*" etc - tried all permutations)?

Any other thoughts or ideas would be most welcome (the prospect of using nested queries, and multiple command buttons + macros (for a VB illiterate like me), is not appealing, not to mention inefficient).

Thanks again for any pearls of wisdom

 

[Norie]

I don't know if it will help but here is a thread on a similar topic

<a href="http://www.mrexcel.com/board2/viewtopic.php?t=107372">Rerturn all records if parameter is blank
</a>

 

[paulhorn]

Thanks for the pointer Norie. This has given me a few ideas on workarounds. I'll try them tonite + let you know

 

[paulhorn]

Norie,
I used the Nz method, and it WORKS!!
I'm not sure (yet) why my IIf's and Nulls were not working, but I think it may be something to do with a text string I was using in the default entry property to guide users adding new records.

Thanks again for your guidance. You are a star.