Access developers already know this, of course, but sometimes we run into situations where we need to be reminded just how important it is.
The human mind thrives on ambiguity. Poetry is the art of making one multi-faceted word speak with many voices. Politicians couldn't survive in office more than a few minutes without the deft word play that conceals as much as it reveals about their true intentions. Comedians depend on the double-entendre to make their audiences roar at the absurd contradictions in their patter.
Yes, without ambiguity, much of human interaction would be drab, colorless and boring.
Yet, as Access developers all know, ambiguity in a database is a short path to data loss, corruption and misleading results. None of which is a good thing.
I was reminded of this yet again in a couple of recent discussions involving dates in forum posts.
Ironically dates are probably the most subtle of the ambiguity problems in Access precisely because Microsoft put a lot of work into making Access pretty good at "guessing" about dates. You can try this yourself by asking Access to evaluate expressions that just LOOK like they should be dates.
Put this in the immediate window and see what you get.
?Datevalue("Jan 3, 2013")
1/3/2013
?Datevalue("Janu 3, 2013") also returns a correct value.
?Datevalue("Jan 3. 2013") does not. I guess you can only push it so far.
Access has become quite good at interpreting and using "date" values. For that reason, we sometimes don't get errors from "bad" dates; we just get wrong or incomplete answers. And that, in my mind is even worse.
One of the problems is that Access actually stores both a date and a time portion for ALL dates, even when one or the other is not supplied.
In other words, if you look under the covers at a field which displays as 01/29/2013, for example, you may find it's actually something like this: 01/29/2013 10:12:23AM.
How does that happen? We'll, let's say you have code that inserts a default value into a date field, like this:
Me.txtStartDate =Now()
Now() as you probably know, means right now, this second. I.e. 01/29/2013 10:12:23AM
And that means in turn, that when you just look at a display of that value, formatted as mm/dd/yyyy, you're going to see only 01/29/2013, which is NOT what is actually in that field. Ambiguity just snuck in the side door through this combination of stored values displayed in an ambiguous fashion.
Most of the time that kind of hidden ambiguity doesn't cause problems, but there are times when it makes a huge difference. Let's say you're trying to match records from two tables on that date field. Inspecting the tables, displayed as mm/dd/yyyy, you'll spot some matches. However, when you ask Access to match the two tables on that date, you're quite likely to get zero, or a very small number of matches. Confusing? You bet.
Maybe you're already ahead of me, but the explanation is quite simple: To Access, 01/29/2013 10:12:23AM is not equal to 01/29/2013 10:12:24AM
Access doesn't stop with the formal display of the field; Access compares the values in their entirety. Reporting on Sales Results which matches dates--with times--of sales against dates--without times--of shipping is going to make you look pretty bad because almost nothing is going to match.
Wait, I just realized, there is some humor here after all. If your Shipping Timeliness report depends on matching Order Dates, including the time, against the Shipping Dates, not including the time, for those orders, the joke's on you, isn't it?
