Cannot get query criteria to work

Access 2010.

I have a form [Enquiries], with a combo box [cboSelectQualification]. The combo box selects a number which I want to use to limit the results of a query.
If there is no number selected i.e. the box is clean, then I want the query to return all values, but when a number is selected, the query must return only results corresponding to that number.
The criteria below works for the case where a number is selected, but results in no records shown if a value is not selected.

IIf(Len([Forms]![Enquiries]![cboSelectQualification])>0,[Forms]![Enquiries]![cboSelectQualification],Like "*" )

This below does also not return anything if nothing is selected.

IIf(Len([Forms]![Enquiries]![cboSelectQualification])>0,[Forms]![Enquiries]![cboSelectQualification],Like "*" & [Forms]![Enquiries]![cboSelectQualification])

Can you spot the problem please
Fritz PaulAsked:
Who is Participating?
 
Gustav BrockCIOCommented:
You can use this trick:

IIf(Len([Forms]![Enquiries]![cboSelectQualification])>0,[Forms]![Enquiries]![cboSelectQualification],[NameOfFieldYouAreFilteringOn])

/gustav
0
 
Fritz PaulAuthor Commented:
Thats awesome!

But was what I did wrong?
0
 
Gustav BrockCIOCommented:
Other methods may work but this trick is so simple.

/gustav
0
 
IrogSintaCommented:
In answer to your question, your LIKE operator should have been outside of your expression like so:
LIKE IIf(Len([Forms]![Enquiries]![cboSelectQualification])>0,[Forms]![Enquiries]![cboSelectQualification], "*" )

As Gustav said, there are a few ways to do this, here's how I normally do it:
LIKE NZ([Forms]![Enquiries]![cboSelectQualification], "*" )

Ron
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.