Solved

MSAccess/VBA/DAO query result set not as expected

Posted on 2007-11-20
3
1,932 Views
Last Modified: 2013-11-27
I have an application that I have to support that was written in MS-Access 2003 VBA with DAO access to ODBC PostgreSQL database tables.

The database was designed without the use of "no nulls" capability of PostgreSQL so a varchar field without valid data could be null, blank or space.

The db.OpenRecordset query is supposed to select all records where a particular varchar field (RecordType) is null, space or blank, but it is not selecting the null fields into the result set.  Can anyone see what might be wrong with the code snippet or suggest an alternative to ensure that nulls are included in the result set?

Please don't suggest changing it to ADO.  This system is in maintenance mode - I have better things to do with my time than rewriting old code.  I just need the result set to be as expected.
Set db = CurrentDb
Set rs = db.OpenRecordset("select * from <tablename> where [supplier] = 'name' and ([RecordType] = null or [RecordType] = '' or [RecordType] = ' ') order by [Sys_ID]")

Open in new window

0
Comment
Question by:ShineOn
  • 2
3 Comments
 
LVL 1

Accepted Solution

by:
GeraldHlasgow earned 500 total points
ID: 20321164
I'm not familiar with PostgreSQL so this is just a guess, but on most SQL databases that I've used if you want to check for a field being null you say

WHERE [FieldName] IS NULL

and not

WHERE [FieldName] = Null

Just a thought...
0
 
LVL 35

Author Comment

by:ShineOn
ID: 20321431
Thanks.  I didn't catch that.  

IS NULL is proper for PostgreSQL - it uses standard SQL.  

Sometimes it takes a second set of eyes... ;)
0
 
LVL 1

Expert Comment

by:GeraldHlasgow
ID: 20321477
Happy to lend an eye or two :-)
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question