Display Records Where Specified Field is NOT NULL?

Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic

   In my SQL statement I want to display records where a particular field is NOT NULL. Seemed like it should be very simple...
I looked around a bit and all I've been able to find is using 

WHERE field IS NOT NULL

or 

WHERE field NOT NULL

Neither of these have worked for me. Can someone let me know what I am doing wrong?

Here is my current SQL statement. I am using an MS Access Database.

strSQL = "SELECT * FROM irr, orders WHERE irr.ID = orders.IRRID AND irr.CLOSED = false AND Serials = IS NOT NULL ORDER BY irr.ID;"

I get this error:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error 
(missing operator) in query expression 'irr.ID = orders.IRRID AND irr.CLOSED = 
false AND Serials = IS NOT NULL'.

If I drop the "IS" and just leave it as "NOT NULL" I just get a blank page returned to me. 