Classic ASP Discussion - Display Records Where Specified Field is NOT NULL?

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

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.