We help IT Professionals succeed at work.

Cannot get query criteria to work

Fritz Paul
Fritz Paul asked
on
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
Comment
Watch Question

Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
You can use this trick:

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

/gustav

Author

Commented:
Thats awesome!

But was what I did wrong?
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Other methods may work but this trick is so simple.

/gustav
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