Using IIF and Like in Query Criteria 3

[gallas]

Hi,

I am trying to collect values from a form and using these as the criteria in a query (which a report will sit on).

To start simply the form has a combo box which the user selects the country to be used in the criteria. Not a problem at all. But what I'm trying to achieve is if the user picks nothing and leaves the box blank then the query should use the wildcard * to select everything.

The following statement in the criteria seems to evaluate the contents of the combo box correctly but will not then use the * to produce a result when the box is Null or 0, etc.

IIf([Forms]![frmCompaniesSelectionFilter]![cboCountries]=Null,([qryCompanyData].[CountryCode]) Like "*",[Forms]![frmCompaniesSelectionFilter]![cboCountries])

If I could I would include a wildcard in the combo box list but unfortunately the table being looked up has two fields: an auto number primary key (CountryCode) and a text field (Country).

Any ideas? %-)

GW.

"If a job's worth doing, it's worth doing twice!"

 

[THWatson]

GW
Would the first condition constructed as follows work?
Like([qryCompanyData].[CountryCode]) & "*"

In other words...
IIf([Forms]![frmCompaniesSelectionFilter]![cboCountries]=Null, Like ([qryCompanyData].[CountryCode]) & "*",[Forms]![frmCompaniesSelectionFilter]![cboCountries])

Tom

 

[TonyJollans]

Hi gallas,

Your first problem is the check for Null.

[purple][tt] [Forms]![frmCompaniesSelectionFilter]![cboCountries]=Null[/tt][/purple]

.. will ALWAYS evaluate to FALSE. You must use ..

[blue][tt] [/tt][red][tt]IsNull([/tt][/red][tt][Forms]![frmCompaniesSelectionFilter]![cboCountries][/tt][red][tt])[/tt][/red][/blue]

Next, I don't see how the results of the IIF fit into a WHERE clause.

If combo is null, you output a full clause (Field LIKE Pattern) ..

[purple][tt] WHERE ... [qryCompanyData].[CountryCode]) Like "*" ...[/tt][/purple]

.. and if combo is not null, you output just a single value ..

[purple][tt] WHERE ... [Forms]![frmCompaniesSelectionFilter]![cboCountries] ...[/tt][/purple]

You must be consistent so that the resultant WHERE clause is always valid. Try using something like ..

[blue][tt] IIf(IsNull([Forms]![frmCompaniesSelectionFilter]![cboCountries]),"",[qryCompanyData].[CountryCode]=[Forms]![frmCompaniesSelectionFilter]![cboCountries])[/tt][/blue]

If that isn't quite right, can you post a couple more details about your two fields and what is in the combo, and the rest of your Where Clause.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[gallas]

Thanks Tom & Tony,

I had tried what you suggest Tom but no joy.

Tony, have tried your new statement. To test whether the IIF IsNull was working I set the form with a Null in the combo box and substituted a value into the truepart of the expression like this:

IIf(IsNull([Forms]![frmCompaniesSelectionFilter]![cboCountries]),4,[Forms]![frmCompaniesSelectionFilter]![cboCountries])

This then returns all the records for England which is 4. So it seems that using - Like "*" as the truepart is the source of the problem.

By the way the SQL looks like this (might help):

SELECT qryCompanyData.*, qryCompanyData.CountryCode, qryCompanyData.Country
FROM qryCompanyData
WHERE (((qryCompanyData.CountryCode)=IIf(IsNull([Forms]![frmCompaniesSelectionFilter]![cboCountries]),4,[Forms]![frmCompaniesSelectionFilter]![cboCountries])))
ORDER BY qryCompanyData.CompanyName, qryCompanyData.CountyName;

I've also noticed that if I use a value in the combo box and then delete it, then I run the query again it returns records matching the previous selection, so maybe I need to check for zero length strings as well. Maybe.

Any ideas?

Garry [Sad]

"If a job's worth doing, it's worth doing twice!"

 

[THWatson]

Garry
I don't know whether or not this will help, but for what it's worth, the Microsoft Knowledge Base shows the following conditional criteria...

Like IIf(IsNull(Forms!FormName![ControlName]), _
"*",[Forms]![FormName]![ControlName])


Tom

 

[gallas]

Hi Tom,

That did it!! The only odd problem now is that once the combo box on the form is cleared the previous entry returns when clicking a btn to run the rpt/qry. It seems shifting focus to another control then running the rpt/qry will reset it. I wonder if it's something to do with it being an unbound combo box. I'll have to investigate.

Anyway do you have the link to that part of the MS site?

Tks, Garry :-D

"If a job's worth doing, it's worth doing twice!"

 

[THWatson]

Garry
The Microsoft Knowledge Base article # is 209645. The title is "How to Use the Query by Form (QBF) Technique"

As for the same value being retained and therefore repeated the next time you run the rpt/qry, it seems to me you need a way to clear (or reset to Null) the value in the combo box. I'm assuming that you have a command button that runs the query, based on the value in the combo box. Can you add a line to the code behind the command button, so that once the query runs the combo box value resets? Something like Me.cboCountries = Null

Tom

 

[lespaul]

I'm confused with your select statement?

SELECT qryCompanyData.*, qryCompanyData.CountryCode, qryCompanyData.Country
FROM qryCompanyData
WHERE (((qryCompanyData.CountryCode)=IIf(IsNull([Forms]![frmCompaniesSelectionFilter]![cboCountries]),4,[Forms]![frmCompaniesSelectionFilter]![cboCountries])))
ORDER BY qryCompanyData.CompanyName, qryCompanyData.CountyName

The red section returns EVERY field in qryCompanyData, but then in the blue section you return two fields AGAIN?

Leslie

 

[gallas]

Thanks Tom,

Everything working fine now.

Resetting the combo box to null using code on the cmd btn works perfectly - I'd never have thought of that.

Garry. [Thumbsup]

"If a job's worth doing, it's worth doing twice!"

 

[gallas]

Hi Leslie,

I'm pretty much a beginner but I'l try to explain. I am setting up the query in design view and the SQL that you are seeing is what is behind it.

qryCompanyData is a saved qry that I am using to bring all sorts of fields together from different tables. I use this query in my qryCompanyDataListFilter as a source of all the fields that my report needs. In the design grid it appears as one field ie qryCompanyData.* and this makes available all the fields the report needs. Then I need a few other fields showing including CountryCode to set my criteria against. Ultimately I will have other fields in the design grid that will get the criteria from my query form.

Hope that explains.

Garry.

"If a job's worth doing, it's worth doing twice!"

 

[lespaul]

Go back into the SQL view and remove the red section and list each field instead of calling them all:

SELECT qryCompanyData.CountryCode, qryCompanyData.Country, qryCompanyData.OtherNeededField
FROM qryCompanyData
WHERE (((qryCompanyData.CountryCode)=IIf(IsNull([Forms]![frmCompaniesSelectionFilter]![cboCountries]),4,[Forms]![frmCompaniesSelectionFilter]![cboCountries])))
ORDER BY qryCompanyData.CompanyName, qryCompanyData.CountyName


Leslie