Help with filtering Query in Access 3

[jeevenze]

I have the following query:

SELECT [tblcatalog].[prodID], [tblcatalog].[catID], [tblcatalog].[prodTitle], [tblcatalog].[prodDesc], [tblcatalog].[prodSize], [tblcatalog].[prodShipInfo], [tblcatalog].[prodPrice], [tblcatalog].[prodSmPic], [tblcatalog].[prodLgPic], [tblcatalog].[artistID], [tblcatalog].[date_inserted], [tblcart].[sold]
FROM tblcatalog LEFT JOIN tblcart ON [tblcatalog].[prodID]=[tblcart].[prodID]
WHERE [tblcart].[sold] = 'Y'

This selects all the records where the value 'Y' is in the 'sold' field.

How can i get the query to select all records where the field 'sold' is empty?

Any help is much appreciated.
Thanks

Jeevenze

 

[kerrigirrl]

i have a query where i search for a date field with no date and i use IS NULL in the criteria. i'm not sure if a character field would be different. or try " ".

 

[bosully]

Have you tried..

WHERE [tblcart].[sold] IS NULL

Once you have no default values set and are not using boolean values for the field, this should work.

Brendan

 

[jeevenze]

WHERE [tblcart].[sold] IS NULL
this worked, thank you.

Someone also suggested
WHERE nz([tblcart].[sold],"&quot=""
and it also works.

 

[AccessSQLUser]

The difference between the 2 suggestions is that WHERE [tblcart].[sold] IS NULL will only look for Null values where as WHERE nz([tblcart].[sold],"&quot="" will also look at empty or blank values .