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 = CurrentDbSet rs = db.OpenRecordset("select * from <tablename> where [supplier] = 'name' and ([RecordType] = null or [RecordType] = '' or [RecordType] = ' ') order by [Sys_ID]")
WHERE [FieldName] IS NULL
and not
WHERE [FieldName] = Null
Just a thought...