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.
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).