Web Wiz
Home
Home		 Hosting Services
Hosting Services		 Knowledgebase
Knowledgebase		 Community and Support Forums
Support Forums		 Customer Login
Customer Login

  New Posts New Posts RSS Feed - Display Records Where Specified Field is NOT NULL?
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Windows Web Hosting 30 Day Free Trial

Windows Web Hosting 30 Day Free Trial

Windows Hosting 30 Day Free Trial, with no obligation, on all our Windows Web and Reseller Hosting Packages.

For more information see our Windows Hosting 30 Day Free Trial page.

Display Records Where Specified Field is NOT NULL?
 Post Reply Post Reply
Author
  Topic Search Topic Search  Topic Options Topic Options
kennywhite View Drop Down
Groupie
Groupie


Joined: 26 February 2009
Location: Indy
Status: Offline
Points: 106 		Post Options Post Options   Thanks (0) Thanks(0)   Quote kennywhite Quote  Post ReplyReply Direct Link To This Post Topic: Display Records Where Specified Field is NOT NULL?
    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. 
Back to Top
WebWiz-Bruce View Drop Down
Admin Group
Admin Group
Avatar
Web Wiz Developer

Joined: 03 September 2001
Location: Poole, England
Status: Offline
Points: 8028 		Post Options Post Options   Thanks (0) Thanks(0)   Quote WebWiz-Bruce Quote  Post ReplyReply Direct Link To This Post 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;"
Find Web Wiz on Facebook
Follow Web Wiz on Twitter
Back to Top
kennywhite View Drop Down
Groupie
Groupie


Joined: 26 February 2009
Location: Indy
Status: Offline
Points: 106 		Post Options Post Options   Thanks (0) Thanks(0)   Quote kennywhite Quote  Post ReplyReply Direct Link To This Post 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. 
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down



Become a Fan on Facebook Follow us on Twitter Web Wiz on Google+ Community Forums Web Wiz Blogs Web Wiz News
Company Information | Contact Web Wiz | Terms & Conditions | Privacy Policy

Prices exclude VAT unless otherwise stated - $, € prices shown as a guideline only
Web Wiz Ltd, Unit 10E Dawkins Road Ind. Est, Poole, Dorset, UK, BH15 4JD
Copyright ©2001-2012 Web Wiz - All rights reserved