Print Page | Close Window

Display Records Where Specified Field is NOT NULL?

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=29548
Printed Date: 29 March 2026 at 7:01pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com


Topic: Display Records Where Specified Field is NOT NULL?
Posted By: kennywhite
Subject: Display Records Where Specified Field is NOT NULL?
Date Posted: 19 July 2011 at 5:28pm
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. 



Replies:
Posted By: WebWiz-Bruce
Date Posted: 20 July 2011 at 7:58am
Try the following:-


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



-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting


Posted By: kennywhite
Date Posted: 20 July 2011 at 3:11pm
Thanks! 

I get a few blank results back, but I'm thinking maybe those records could have a blank space in the field. 



Print Page | Close Window

Forum Software by Web Wiz Forums® version 12.08 - https://www.webwizforums.com
Copyright ©2001-2026 Web Wiz Ltd. - https://www.webwiz.net