Finding and Replacing Nulls in Access

June 23rd, 2009 by datapig Leave a reply »

Every now and then, I come across quirks in Excel or Access that leave me scratching my head.  Today's post highlights an Access quirk that has bothered me for some time.

In Access, you'll often need to replace one set of values with another. If your table is fairly small, you can bypass the old Action Query, and use Find-and-Replace.

For example, in this screenshot, I'm replacing all the instances of $0.00 with a blank (Null value). This works fine. No big deal.

HOWEVER…

If I try to find and replace null values with $0.00, you'll notice that all the buttons (except for Cancel) are disabled.

Bastards!    This works in Excel just fine.  Why doesn't it work in Access? 

The answer to this dilemma is to explicitly enter 'Null' into the 'Find What' field.

At this point, the buttons are enabled again, and Access will find the Null values and replace them with $0.00.

But again, my question is why?  Maybe it's to protect us monkeys from throwing scat into our Access tables.

Like I said, this one leaves me scratching my head (like a monkey).

Advertisement

11 comments

  1. Martin says:

    Dear BaconBit – This is just a short note to say how much I’m enjoying your posts. Excellent stuff. Keep up the good work.
    m

  2. jeffrey weir says:

    Mike – a blank is not a null value, and a null value is not a blank. If you leave the field blank, then it’s still waiting for you to tell it what to select…and it doesn’t know if you want Nulls, Peanuts, or Bacon.

    So a blank is no selection, and a null is an absence of any value of any kind.

    Another case of operator error. No doubt there’s a few more for us to iron out ( Find and Replace?) given your comment “I come across quirks in Excel or Access that leave me scratching my head”. :-)

  3. DataPig says:

    Jeffrey: If I don’t put anything into a field or a textbox in Access, the value attribute for that field or texbox is considered to be Null. Why would it not be the same rule in Find-and-Replace.

    The Find-and-Replace dialog box in Excel seems to understand/infer that if you don’t enter anything into the search parameters, you are looking for null or IsEmpty.

  4. Tony Rose says:

    I love Excel’s find and replace for this very reason. And as we all know, a null is definitely not the same as a zero. Perfect example is =average()

    Nice to see you join the blog scene Mike!

  5. datapig says:

    Tony: Thanks!

  6. jeffrey weir says:

    Having never actually used Access, I’m just extrapolating my scant understanding of SQL Server to yet another platform I havent mastered ;-)

    Interestingly, when I import data into excel from SQL Server, then (from memory) any null values are tagged as excel as “(blank)”. So it throws a text field in there, i think, and ruins a pefectly nice null value.

    With regards to why Access allows you to use blanks as null when inputting, but not when searching; I’d imagine this could be intentional for the following reason:

    When inputting stuff, fields start off as NULL, unless the user inputs something. There’s little room for misunderstanding.
    But with regards to search, the programmers probably want to make damn sure that the user is indeed searching for a null, rather than the case when a user is searching for a zero or an empty string and mistakenly thinks that leaving the ‘find’ box blank is the same a a zero or empty string.

    It wouldn’t be good if the user thought that leaving the FIND box blank would do one thing, but instead it did another. Much safer to make the user explicitly state that they are searching for nulls.

    Like I said above…complete hole in my ass talk.

  7. Rachel says:

    Thank you so much!!!!! I’ve been trying for about 15 minutes to replace a blank field in my Access table after adding a new column.

  8. Syeda says:

    You have made my day today. I have been trying to do this for an hour. I am wondering though that in Access 2007 I could put ” ” to find a blank cell, but in Access 2010 I explicitly have to put in Null.
    Thanks for this :)

  9. Bilal says:

    Thanks

  10. Swasti says:

    REPLACING MULTIPLE VARAIBLES:
    I had a tad different issue. I had a list of 24 varibles like 6/1/14, 2/1/14, 3/1/14… that repeated on 20010 rows and I had to replace them with a single variable
    Put in * in Find and I cld replace all the variables with the 11/1/2014 rev date I wanted.

    This tip her but made me use my mind beyond rather apparent way to use Find and Replace
    Thanks

  11. Swasti says:

    REPLACING MULTIPLE VARAIBLES:
    I had a tad different issue. I had a list of 24 varibles like 6/1/14, 2/1/14, 3/1/14… that repeated on 20010 rows and I had to replace them with a single variable
    Put in * in Find and I cld replace all the variables with the 11/1/2014 date I wanted.

    This tip here but made me use my mind beyond rather apparent way to use Find and Replace
    Thanks

Leave a Reply

Powered by sweet Captcha