Simple Access 2010 Question

Factual Questions
1

Either something has changed with Access 2010, or I’m doing something stupid or very possibly both.

I used to use Access regularly, but not so much in the past few years. I was asked to make some updates to a database that I created many years ago. I updated the majority of the records, but I knew there would be some that wouldn’t update because the key field I was linking to could be blank.

So, I’ve got a couple of tables and I want to find any records where the ID field is blank. It used to be simple to use the query builder and just put IsNull or IsMissing in the criteria line for the ID field. This no longer works in 2010. It recognizes them as functions, but then it says it needs an expression after. I’ve tried entering the table and field name but I’m still getting no results.

I’m sure this is a very simple thing to do but I’ve been messing around with it for a while and thought I’d see if one of you can beat me to the solution.

2

Dang. I only have Access 2007 and 2003 here. I have 2010 at home so I could check later.

Put the IsNull on the field line. Something like ID_IS_NULL: IsNull(ID). In the criteria put True or 1. Do not put quotation marks around True.

3

Very close… -1 is what it returns for True. That did the trick, but it seems more complicated than the previous versions.

Thank you much.

4

That’s weird. I’ve been using Access since 2.0 and use Access 2010 at work, and the query seems to be exactly the same (outer join with IsNull as the criterion for the outer table field).

And hasn’t Access always used -1 for True? I know that it always throws me.

5

This was a query hitting only one table so there was no outer join, but I would expect it to work the same and I’m 99% positive I had done it this way against one table in earlier versions.

Yes, I believe -1 has always been True in Access. I use several different query tools though for different databases and that and also what is the wildcard character are ones I always have to try a couple of times to see which works for which system.

6

The -1 for True is strange. In other databases it’s 1 or any number not 0.

0 = false for boolean values in all databases that I’ve encountered.

7

You need to use IS NULL (with a space).

8

There have always (to the best of my knowledge) been two very similar constructs in Access:

**ISNULL(var) **is a VBA function that returns True or False depending on whether the variable being passed is null

field IS NULL is a query criterion that filters on the basis of null values.

Similar, but not identical. Learn the differences between Is Null and IsNull() in Access | TechRepublic

This is slightly counter-intuitive, but makes sense when you consider the binary representation of True and False in Access. I agree that it’s perhaps not the best way of doing things, specially as it’s very confusing across multiple databases. Discussion here: sql - Why is "Yes" a value of -1 in MS Access database? - Stack Overflow