We help IT Professionals succeed at work.

Cannot get query criteria to work

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

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




Thats awesome!

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

Other methods may work but this trick is so simple.

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], "*" )