Criteria to return ALL including Null values 1

[gallas]

Hi, I have a problem with returning all records. The query gets its criteria from a user form. If the user leaves a field blank (Null) this means return all. The code I have uses an asterix as the criteria if the user enters nothing. However, to find all fields I need to use * OR IsNull otherwise Null value fields are not returned. The problem is I cannot get both from my statement. Its the IIF that I'm having trouble with. Here is the relevant part of the query:

Like IIf(IsNull([Forms]![frmCompaniesSelectionFilter]![TboZip]),"*",[Forms]![frmCompaniesSelectionFilter]![TboZip])

(There are about half a dozen of these in my query. Some don't matter because the value is from a CBO on the form.)

Of course the alternative is to change all Null values in the table to zero length strings and set field default value to "".

Can anyone help. Garry. :-(


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

 

[Remou]

How about changing the field in query design view to read:

SearchMe: Trim(FieldName & "")

This means that Null fields will equal a zero length string, which is fine with wild cards.

 

[PHV]

Replace this:
Like IIf(IsNull([Forms]![frmCompaniesSelectionFilter]![TboZip]),"*",[Forms]![frmCompaniesSelectionFilter]![TboZip])
with this:
=[Forms]![frmCompaniesSelectionFilter]![TboZip] OR [Forms]![frmCompaniesSelectionFilter]![TboZip] Is Null

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[gallas]

Hi PH,

When I put the new code in the query I get a parameter prompt for Forms!frmCompaniesSelectionFilter!cboCountries.

Here is the complete original code

SELECT qryCompanyData.*
FROM qryCompanyData
WHERE (((qryCompanyData.CountryCode) Like IIf(IsNull([Forms]![frmCompaniesSelectionFilter]![cboCountries]),"*",[Forms]![frmCompaniesSelectionFilter]![cboCountries])) AND ((qryCompanyData.CountyID) Like IIf(IsNull([Forms]![frmCompaniesSelectionFilter]![cboCounty]),"*",[Forms]![frmCompaniesSelectionFilter]![cboCounty])) AND ((qryCompanyData.IndustryCodePrimary) Like IIf(IsNull([Forms]![frmCompaniesSelectionFilter]![cboMainIndustry]),"*",[Forms]![frmCompaniesSelectionFilter]![cboMainIndustry])) AND ((qryCompanyData.StatusCode) Like IIf(IsNull([Forms]![frmCompaniesSelectionFilter]![cboStatus]),"*",[Forms]![frmCompaniesSelectionFilter]![cboStatus])) AND ((qryCompanyData.SalesRepID) Like IIf(IsNull([Forms]![frmCompaniesSelectionFilter]![cboArea]),"*",[Forms]![frmCompaniesSelectionFilter]![cboArea])) AND ((qryCompanyData.[Town/City]) Like IIf(IsNull([Forms]![frmCompaniesSelectionFilter]![TboTown]),"*",[Forms]![frmCompaniesSelectionFilter]![TboTown])) AND ((qryCompanyData.[Post/ZipCode]) Like IIf(IsNull([Forms]![frmCompaniesSelectionFilter]![TboZip]),"*",[Forms]![frmCompaniesSelectionFilter]![TboZip])))
ORDER BY qryCompanyData.CompanyName;

Here is the code with the new bit

SELECT qryCompanyData.*
FROM qryCompanyData
WHERE (((qryCompanyData.CountryCode) Like IIf(IsNull([Forms]![frmCompaniesSelectionFilter]![cboCountries]),"*",[Forms]![frmCompaniesSelectionFilter]![cboCountries])) AND ((qryCompanyData.CountyID) Like IIf(IsNull([Forms]![frmCompaniesSelectionFilter]![cboCounty]),"*",[Forms]![frmCompaniesSelectionFilter]![cboCounty])) AND ((qryCompanyData.IndustryCodePrimary) Like IIf(IsNull([Forms]![frmCompaniesSelectionFilter]![cboMainIndustry]),"*",[Forms]![frmCompaniesSelectionFilter]![cboMainIndustry])) AND ((qryCompanyData.StatusCode) Like IIf(IsNull([Forms]![frmCompaniesSelectionFilter]![cboStatus]),"*",[Forms]![frmCompaniesSelectionFilter]![cboStatus])) AND ((qryCompanyData.SalesRepID) Like IIf(IsNull([Forms]![frmCompaniesSelectionFilter]![cboArea]),"*",[Forms]![frmCompaniesSelectionFilter]![cboArea])) AND ((qryCompanyData.[Town/City]) Like IIf(IsNull([Forms]![frmCompaniesSelectionFilter]![TboTown]),"*",[Forms]![frmCompaniesSelectionFilter]![TboTown])) AND ((qryCompanyData.[Post/ZipCode] =[Forms]![frmCompaniesSelectionFilter]![TboZip] OR [Forms]![frmCompaniesSelectionFilter]![TboZip] Is Null )))
ORDER BY qryCompanyData.CompanyName;

Will the new code work anyway? If a user enters MK* in the TboZip field will not the suggested code return MK* and ALL Null values? Or have I misunderstood? What we need is when a user leaves the field empty on the form the query should return ALL fields those containing data and those with Null value. Make any sense?

Tks Garry.

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

 

[gallas]

Sorry Remou, not sure how to use that bit of code in the query with the criteria string.

Garry.

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

 

[Remou]

For example:

WHERE (((Trim(qryCompanyData.CountryCode & "")) Like

 

[PHV]

SELECT *
FROM qryCompanyData
WHERE (CountryCode=[Forms]![frmCompaniesSelectionFilter]![cboCountries] OR [Forms]![frmCompaniesSelectionFilter]![cboCountries] Is Null)
AND (CountyID=[Forms]![frmCompaniesSelectionFilter]![cboCounty] OR [Forms]![frmCompaniesSelectionFilter]![cboCounty] Is Null)
AND (IndustryCodePrimary=[Forms]![frmCompaniesSelectionFilter]![cboMainIndustry] OR [Forms]![frmCompaniesSelectionFilter]![cboMainIndustry] Is Null)
AND (StatusCode=[Forms]![frmCompaniesSelectionFilter]![cboStatus] OR [Forms]![frmCompaniesSelectionFilter]![cboStatus] Is Null)
AND (SalesRepID=[Forms]![frmCompaniesSelectionFilter]![cboArea] OR [Forms]![frmCompaniesSelectionFilter]![cboArea] Is Null)
AND ([Town/City]=[Forms]![frmCompaniesSelectionFilter]![TboTown] OR [Forms]![frmCompaniesSelectionFilter]![TboTown] Is Null)
AND ([Post/ZipCode]=[Forms]![frmCompaniesSelectionFilter]![TboZip] OR [Forms]![frmCompaniesSelectionFilter]![TboZip] Is Null)
ORDER BY CompanyName

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[gallas]

Thanks Remou, your help greatly appreciated.

Your suggestion works fine (I should have read yr first reply more carefully). My code is now:

SELECT qryCompanyData.*
FROM qryCompanyData
WHERE (((qryCompanyData.CountryCode) Like IIf(IsNull([Forms]![frmCompaniesSelectionFilter]![cboCountries]),"*",[Forms]![frmCompaniesSelectionFilter]![cboCountries])) AND ((qryCompanyData.CountyID) Like IIf(IsNull([Forms]![frmCompaniesSelectionFilter]![cboCounty]),"*",[Forms]![frmCompaniesSelectionFilter]![cboCounty])) AND ((qryCompanyData.IndustryCodePrimary) Like IIf(IsNull([Forms]![frmCompaniesSelectionFilter]![cboMainIndustry]),"*",[Forms]![frmCompaniesSelectionFilter]![cboMainIndustry])) AND ((qryCompanyData.StatusCode) Like IIf(IsNull([Forms]![frmCompaniesSelectionFilter]![cboStatus]),"*",[Forms]![frmCompaniesSelectionFilter]![cboStatus])) AND ((qryCompanyData.SalesRepID) Like IIf(IsNull([Forms]![frmCompaniesSelectionFilter]![cboArea]),"*",[Forms]![frmCompaniesSelectionFilter]![cboArea])) AND ((Trim([qryCompanyData].[Town/City] & "")) Like IIf(IsNull([Forms]![frmCompaniesSelectionFilter]![TboTown]),"*",[Forms]![frmCompaniesSelectionFilter]![TboTown])) AND ((Trim([qryCompanyData].[Post/ZipCode] & "")) Like IIf(IsNull([Forms]![frmCompaniesSelectionFilter]![TboZip]),"*",[Forms]![frmCompaniesSelectionFilter]![TboZip])))
ORDER BY qryCompanyData.CompanyName;

I understand what Trim does to strings. But how does this work with Null values?

Thanks again,
Garry. :-D

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

 

[gallas]

Hi PHV,

Gave yr code a try. Bombs out with a Query too complex error. Creates a mass of entries in the design grid.

Tks anyway,

Garry.

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

 

[Remou]

You are adding "" to the null field and null field & "" = "", which is a zero-length string, not a null field.

 

[gallas]

Got it, thanks.

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

 

[PHV]

And this ?

SELECT *
FROM qryCompanyData
WHERE CountryCode & '' Like Nz([Forms]![frmCompaniesSelectionFilter]![cboCountries],'*')
AND CountyID & '' Like Nz([Forms]![frmCompaniesSelectionFilter]![cboCounty],'*')
AND IndustryCodePrimary & '' Like Nz([Forms]![frmCompaniesSelectionFilter]![cboMainIndustry],'*')
AND StatusCode & '' Like Nz([Forms]![frmCompaniesSelectionFilter]![cboStatus],'*')
AND SalesRepID & '' Like Nz([Forms]![frmCompaniesSelectionFilter]![cboArea],'*')
AND [Town/City] & '' Like Nz([Forms]![frmCompaniesSelectionFilter]![TboTown],'*')
AND [Post/ZipCode] & '' Like Nz([Forms]![frmCompaniesSelectionFilter]![TboZip],'*')
ORDER BY CompanyName

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886