"Is null" not working in Tables

[BernieAnne]

I bought a new computer with Windows 10 and updated to MS Office 2016. I was previously using Office 97/updated to Office XP. My Access database was transferred from the old to the new computer onto Access 2016.

In 'Tables' when I filter to 'equals blank' and then use 'replace', the "is Null" is not working. I keep getting the message "The search item was not found." This happened yesterday and I was able to use "null" and it worked. However, today I can't get either to work at all.

Does anyone have any idea what the problem is?

Thanks for any help that you can provide.

~ Bernie

 

[isladogs]

In 'Tables' when I filter to 'equals blank' and then use 'replace', the "is Null" is not working.

An empty or zero length string (ZLS) is not the same thing as null

If you want to update empty strings to Null, use an update query filtering for "" and updating to Null

 

[BernieAnne]

Thanks for your reply, Ridders. I am not computer literate and it was 14 years ago since I set up my database. I'm afraid I'm not understanding your reply.

In the past on the old version of Access, while in tables I would click onto a blank cell, click on filter, then click onto replace, type in is null and then type in the info I wanted to fill the blank cells with in that field.

For example:

Find what: is null
Replace with: January 2018

As far as I know, I'm not doing anything different in Access 2016, however, I can't get it to work and I have no idea why.

 

[The_Doc_Man]

As Colin points out, there are two possible 'values' that look alike. A zero-length string (also called an empty string) can be detected with IsEmpty(). A null field would occur only if the method you used to create the new record did not provide ANYTHING for that field - i.e. not "", but rather that the field wasn't mentioned at all when you did the insert or import that loaded that record.

Nulls do not play well with expressions including comparison functions. This is why you were having trouble. Believe it or not, if you had a null field and compared it to another null field, they are not equal. Yes, that's right... null is NOT equal to null. That is why you need the special IsNull function for the test.

 

[BernieAnne]

Thanks, Doc Man. I'm not understanding why it worked on Access 97 but not on Access 2016. I've been doing this for the past 14 years without any problems.

 

[CJ_London]

try finding 'null', rather than 'is null'

works for me on numeric fields

as to why it is different - your old db uses JET, modern ones use ACE, a different db engine so there may be some differences in what works and what doesn't

 

[The_Doc_Man]

CJ pointed out the important difference: Some time after Ac2002, Access's database engine was upgraded from something called Jet to a new engine called Ace. Here are your syntax choices for SQL, all of which are approximately the same in effect.

SELECT [I]<somefields>[/I] FROM [I]<sometable>[/I] 
    WHERE ( [I]<somefield>[/I] [COLOR="Purple"]IS NULL[/COLOR] ) OR  ( [I]<somefield>[/I] = "" ) ... ;

...

SELECT [I]<somefields>[/I] FROM [I]<sometable>[/I]
    WHERE ( [COLOR="purple"]ISNULL( [I]<somefield>[/I] )[/COLOR] = TRUE ) OR ( [COLOR="purple"]ISEMPTY( [I]<somefield>[/I] )[/COLOR] = TRUE ) ...  ;

'    the 2nd one can actually leave off the " = TRUE " since that is the default for SQL

SELECT [I]<somefields>[/I] FROM [I]<sometable>[/I]
    WHERE ([COLOR="purple"] ISNULL( [I]<somefield>[/I] )[/COLOR]  OR[COLOR="purple"] ISEMPTY( [I]<somefield>[/I] )[/COLOR] )...  ;

'    this simplifies it even more

SELECT [I]<somefields>[/I] FROM [I]<sometable>[/I]
    WHERE [COLOR="purple"]NZ( [I]somefield[/I], "" )[/COLOR] = "" ;

And of course, in VBA you can do an IF that uses the IS NULL syntax or the ISNULL() function.

 

[Ranman256]

IS NULL
is for query criteria

IsNull(field) is for vb code

 

[The_Doc_Man]

True that ISNULL is preferred for VBA, RANMAN. However, you CAN say "IF X IS NULL" in VBA and it will work, since it is possible in VBA to have a Variant type variable that is null. The IS NULL syntax is not so useful for the other VBA data types, though, because they can't be null. Usually I use ISNULL as a VBA function. I was only listing possibilities.

 

[Pat Hartman]

I prefer to never allow ZLS in my text fields because of the confusion. Various versions of Access have over the years vacillated on the default. I'm pretty sure that the default setting for AllowZLS in 97 was No but the default setting for A2016 is Yes. So, in the past, ZLS were never allowed to be stored in the table and therefore checking for Null gave you what you want. Today, you probably have ZLS in some columns.

I have complained many times to the MS Access team regarding this. They think it is easier for novices if ZLS is allowed. However, once you get to using queries, SQL treats the two values as different and so you have trouble with queries. My pending request is for this to be made an option at the database level so people can choose their poison.

To get yourself out of this mess. you have two options.
1. Fix the data
2. Fix all the queries AND VBA

Personally, I would fix the data. To do that, you have to go through every table to identify if there are any ZLS lurking. The simplest method is to simply change AllowZLS from Yes to No and save the table. The table won't save if any row contains a ZLS. Then you can stop, fix the data and move on to the next table.

There are two common ways for ZLS to get into your table.
1. You import Fixed Width files created on the main frame. If AllowZLS is ste to Yes, the trailing spaces or "empty" strings are imported as ZLS.
2. A user entering data in a form, changes his mind about a value. Instead of selecting the value and using the delete key, he either backspaces or types over the field with the space key. This results in a ZLS and if AllowZLS is set to Yes, a ZLS gets saved. If the property is defined as No, the field is null.

I prefer to always use Null as the unknown value because that makes for consistency between text and numeric and date fields. And because I know that I always use null as the default (unless I actually have a default I want), I account for the nulls in queries and expressions.

Never allowing nulls results in having to account for specific "unknown" values. For example a date with a default value of 0 is actually Dec 30, 1899 which most people find confusing. But if you don't want to allow a null date, the date has to be something! Many people use bogus future dates or bogus old dates. I prefer null for unknown. For numeric values, sometimes 0 as a default is innocuous. In other cases it is dangerous because 0 is a valid value. Do you want to define a grade as 0? That will wreck a student's GPA if he hasn't taken a specific test yet or if you haven't entered the grade. It's also hard to find unrecorded grades since there is no way to distinguish an earned 0 from a default 0. For money and quantity amounts, using 0 as the default is innocuous but for consistency, I always use null.

Using Null as the default means that you may have to define certain fields as required because you can't have them empty. For example, it makes absolutely no sense at all to have 0 as the default for foreign keys (this is the current state of Access because like ZLS, the PTB have decided to "protect" newbies from the horrors of null by defaulting all numeric values to 0). The default for the FK should ALWAYS be null and the field should ALWAYS be defined as required. If you don't have a FK, you have an orphan.

 

[The_Doc_Man]

I agree with Pat though there was a spirited debate about use of null, zero, and "" (the empty string) for various reasons.

My own viewpoint of dates is that for MOST modern things, a date of zero isn't bad because my validation code simply tested for dates after 1-Jan-1970 and any dates before that time were before the invention of the PC (and also before the start of "epoch" for UNIX machines).

That, of course, excludes any machines that are keeping birthdays, because in that case, mine is also before 1970. And for all you snark-meisters out there, it doesn't matter that Windows actually CAN interpret negative dates as pre-1900. I don't need THAT particular ability anyway. I'm younger than that!

Anyway, the point is that use of nulls should specifically mean "not provided" or "no information available" or things of that ilk. Oddly enough, wse of ZLS is actually frowned upon in some aspects of law and government.

For example: A government document with a page that, rather than being blank, instead says "This page intentionally left blank." To which you are allowed to think about morons, oxy- and plain. 'cause of course when you put up the blank page disclaimer, the page isn't blank anymore anyway. So there is no need to say it was blank when in fact if you had said nothing, it would have been blank anyway!

Also: In legal depositions, when all the questions are asked and there is a final question such as "Do you have anything else to add?", rather than providing blanks after the answer, you SOMETIMES see the phrase, "Further, deponent sayeth not." (Fortunately, that latter abomination is becoming less common.)

 

[CJ_London]

Although I haven't done any testing to prove the point I believe there is another benefit of using nulls rather than zls in certain situations - indexing.

indexes can be set to ignore nulls, which for fields that are frequently not populated makes for faster operation since the index data will be smaller. However a zls will be indexed, increasing the index size because of the pointer - and affecting criteria performance because you need to check for both null and zls to be safe. And I also suspect the search algorithm will be less efficient - they don't cope so well with large numbers of same values because it then has to revert to a sequential search through those same values. You should never index booleans for example.

 

[BernieAnne]

Thanks to everyone for all of your replies. Unfortunately, most of what you said was way over my head. I'm going to have to live with the fact that 'is null' and 'null' no longer works. It was much faster just going into the table at the end of the month and adding the Month and Year and the name of the fuel station once, rather than having to add it several hundred times with copy and paste.

 

[The_Doc_Man]

It was much faster just going into the table at the end of the month and adding the Month and Year and the name of the fuel station once

The fact that this worked for you at all means you have a table design flaw somewhere, actually. This kind of statement smells of denormalization in some sense. Specifically, that somehow you are mixing high-level and low-level data in the same table. Or perhaps overview and detail data, if you prefer to look at it that way.

Just remember that "faster" doesn't always mean "better" - OR "correct."

 

[BernieAnne]

Just giving everyone an update. Two weeks ago Microsoft did an update to my computer, this being the first update since I bought the computer back in February. Since the update, is null now works once again just like it did for the past 20 years on my previous two computers. So obviously whatever glitch or item that was missing was corrected with the update.

Thanks again to everyone for all of your help in trying to solve this mystery.

 

[Galaxiom]

There are two common ways for ZLS to get into your table.
2. A user entering data in a form, changes his mind about a value. Instead of selecting the value and using the delete key, he either backspaces or types over the field with the space key. This results in a ZLS and if AllowZLS is set to Yes, a ZLS gets saved.

In my experience (A2003 to A2010) backspacing or entering spaces results in a Null even if ZLS is allowed for the field. BTW Trailing spaces are trimmed but not leading spaces.

ZLS can be entered using a query.

Never allowing nulls results in having to account for specific "unknown" values. I prefer null for unknown. For numeric values, sometimes 0 as a default is innocuous. In other cases it is dangerous because 0 is a valid value. Do you want to define a grade as 0? That will wreck a student's GPA if he hasn't taken a specific test yet or if you haven't entered the grade.

Exactly. It is important to note that SQL aggregate functions like Avg, Count, Min, Max (and the equivalent Domain functions) applied to a field will ignore Nulls in the calculation but include zero values. It can make a world of difference.

 

[The_Doc_Man]

Aha! The dreaded "Microsoft patch" syndrome. BernieAnne, consider that I am at least symbolically offering you my shoulder to cry on. I can't tell you how many times someone has been burned by that problem. Just watch out for Win10 patch #1803, which is known to eat your lunch for you in many different ways at the same time.

 

[gemma-the-husky]

I tihnk a long while ago I had to change some queries because null was being handled strangely when using a different access version, so maybe it's something MS fixed.

Also I don't recall seeing "The search item was not found"

I do occasionally see "The search key was not found in any record", and generally that seems to indicate corruption.

 

[Pat Hartman]

Access has brought me to tears today. I can't believe I got whacked AGAIN with an update that breaks functionality. I don't have time for this s*#@

Today my eval()'s aren't working. I don't have a clue where to start to work around this.

 

[Mark_]

Just giving everyone an update. Two weeks ago Microsoft did an update to my computer, this being the first update since I bought the computer back in February. Since the update, is null now works once again just like it did for the past 20 years on my previous two computers. So obviously whatever glitch or item that was missing was corrected with the update.

Thanks again to everyone for all of your help in trying to solve this mystery.

You may want to double check to see if this still works after each update.

MS is known for "Fixing" that which isn't broken.

Were it me, I'd look for a better fix than going in and manually updating a table.