Where showing Nulls

[Jeffreyb723]

What I was looking to do was to make my query show everything including Nulls when there was nothing in txtCustomer and to not show nulls when there was something in there. I came up with this:

WHERE (tblCompanies.CompanyName) Like "*" & [Forms]![frmSearch]![txtCustomer] & "*" OR [Forms]![frmSearch]![txtCustomer]

However, it does exactly the opposite of what I wanted it to do and frankly it makes no sense to me. Shows everything except Nulls when txtCustomers is null and shows everything with nulls when any character is in txtCustomers.

 

[jdraw]

How does the form relate to the table?

query show everything including Nulls when there was nothing in txtCustomer and to not show nulls when there was something in there.

Query along this format should do what you're asking: (untested)

SELECT IIF(IsNull(CompanyName), "NULL",CompanyName)
FROM tblCompanies

 

[spikepl]

(tblCompanies.CompanyName) Like "*" & [Forms]![frmSearch]![txtCustomer] & "*"

can never show CompanyNames that are null, because the criterion for showing Null is

CompanyName IS Null

When txtCustomer is null, then that part shows any record but the ones with Null, so all is as it should be.

IN the expression

OR [Forms]![frmSearch]![txtCustomer]

the value of [Forms]![frmSearch]![txtCustomer] is used as Boolean. 0 means False, but anything else means True,hence all records are displayed, becasue the condition is not related to the content of the record.


WHERE (tblCompanies.CompanyName Like "*" & [Forms]![frmSearch]![txtCustomer] & "*" And [Forms]![frmSearch]![txtCustomer] NOT Is Null) Or [Forms]![frmSearch]![txtCustomer] Is Null