Selection Box - Display records where a field is blank

[WantoN]

Hi guys,
I'm trying to set up a drop-down combo box which contains all the records from table 'Company' that have either no data for PostalCode or CompanyType.

Below is what I've tried to use, evidently it doesn't work; it displays nothing in the drop down, and only one row.

SELECT Company.CompanyID, Company.CompanyName FROM Company WHERE Company.PostalCode = " " OR Company.CompanyType = " " ORDER BY Company.CompanyName;

Thanks guys!
Antoni

 

[Trevil]

You should change the test to 'Is Null' or "" instead of the spaces (or you can add as additional test if you prefer). The database does not store trailing spaces. If no value was ever stored, then you should test for Null, or it is possible the value could be 'Empty' (i.e. "")

"Hmmm, it worked when I tested it....

 

[WantoN]

Thanks mate, but I'm still having trouble, using both Is Null and "" (which is what I had originally).

With : SELECT Company.CompanyID, Company.CompanyName FROM Company WHERE Company.PostalCode = 'Is Null' OR Company.CompanyType = 'Is Null' ORDER BY Company.CompanyName;

I get the same problem, whereby the dropdown box displays only one blank row.

by the way, bound colums and column count are both set to 2 at present.

Cheers,
Antoni

 

[Trevil]

Remove the single-quotes from 'Is Null'

The following works for me:
SELECT Company.CompanyID, Company.CompanyName
FROM Company
WHERE (((Company.PostalCode) Is Null)) OR (((Company.CompanyType) Is Null))
ORDER BY Company.CompanyName;


"Hmmm, it worked when I tested it....

 

[WantoN]

Perfect!!! Thanks muchly mate.

Antoni

 

[PHV]

And for testing in one shot for Null, ZeroLengthString and Blank:
SELECT CompanyID, CompanyName
FROM Company
WHERE Trim(PostalCode & '') = '' OR Trim(CompanyType & '') = ''
ORDER BY 2;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886