Microsoft Access - Unable to return all records in query when combobox is blank

Hi!

This is driving me mad, and it should be really simple! I'm hopefully missing something really obvious that you guys may be able to help me with.

I have a query that gets its criteria from a combobox on a form. This works when a value is in the combobox, but when it's blank I want to display all records.

This is the simple SQL that I'm using for testing.  It does return 2 if the combobox is blank, but as soon as I replace the "2" with "*", I get 1 blank record when there should be over 12k with data. (There are no blank records in this table / field)

SELECT tblChecks.Team
FROM tblChecks
WHERE (((tblChecks.Team)=IIf([forms]![frmReportMenu].[cmbTeam]="","2",[forms]![frmReportMenu].[cmbTeam])));

Any ideas where I'm going wrong here?

Thanks

Pete

Use the Like operator:

SELECT tblChecks.Team
FROM tblChecks
WHERE tblChecks.Team Like IIf([forms]![frmReportMenu].[cmbTeam]="","2",[forms]![frmReportMenu].[cmbTeam]);

SELECT tblChecks.Team
FROM tblChecks
WHERE (tblChecks.Team= [forms]![frmReportMenu].[cmbTeam] or forms]![frmReportMenu].[cmbTeam] & "" = "");

The criteria compares Team to the form field which will return just the selected item and the "OR" clause returns true if the combo is empty and so will select all.

Wally,
There is no reason to use Like without any wild cards.  Using Like sometimes prevents the database engine from using indexes.  So having criteria such as "somefield Like 2" could force a full table scan when an index read would have gotten the record immeatiately for "somefield = 2"

Pat,

Many thanks for this, it works a charm!

At the moment most of my queries are built on the fly through VBA which causes bloat, so I'm trying to build dedicated queries and you've pointed me in the right direction with this.

Cheers

Pete

Hi wally eye!

Many thanks for helping me with this.

I need to study SQL in more depth I think.

Pete

Hi Pat,

I'm trying to extend this query to include multiple criteria and I'm struggling.

Using the same query as before, I've added another field that should either return all records if the control is blank, or filter if the control has some criteria.

SELECT tblChecks.Team, tblChecks.PID
FROM tblChecks
WHERE (((tblChecks.Team)=[forms]![frmReportMenu].[cmbTeam] OR [forms]![frmReportMenu].[cmbTeam] & "" = "" ) AND ((tblChecks.PID)=[forms]![frmReportMenu].[cmbTeamMember] OR [forms]![frmReportMenu].[cmbTeamMember] & "" = ""));

All I get when I try this is "the expression is typed incorrectly, or its too complex to be evaluated"

The first field Team is a text field in the table, whilst PID is a number (long integer).

Whilst using just one criteria at a time for testing, I haven't been able to get it to return the correct results.

Thanks again for any help.

Pete

Welcome back Pat.  You obviously are much better at this than I, I'll take your advice on the Like.  Thank you.

I've been working out of town on a project with a looming deadline but it should quiet down in another month.

The syntax looks OK but I am no good at parsing the () and [] pairs with my eyes.  Start by removing all of them and then just put back the two sets of () that you need.

(a OR b) AND (c OR D)