Before we started using databases and spreadsheets, we probably never thought about data types - the difference between text and numbers and dates and all that. A few bewildering sort results and a couple of type-mismatch errors later (plus the one about the ZIP codes with the missing zero in the front), and we're all the wiser.
We learned the rules for queries: Use quotes for text but not for numbers. Use a special sign to quote dates: {4/1/98} in FoxPro, or #4/1/98# in Access.
In Access, you get full-time data-type help along these lines in the query design window. Use a wildcard for a field criteria, and the program will automatically quote the criteria and add the keyword LIKE. Use a date for a criteria, and it will automatically put the pound signs around it. Of course, you are on your own if you type the query text yourself in the SQL window.
Now to the point. The column this month comes from a question we got at NICAR recently. There is a short answer to make the query work. The long answer involves understanding how Access does things so you'll understand why things work the way they do. Knowing your software is not just technical trivia or the mastering of minutiae to impress people at work, although you can get by for quite some time without the ordeal of reading the fine print in the manual - or a long-winded tech tip. Eventually, doing your journalistic duty to know exactly how and why you got a certain answer will require you to look behind the curtain.
It is possible to type a query in Access, using a date criteria, which Access decides is just peachy, syntax-wise, and you'll get an answer back that will make you think: "My query didn't work" or "This can't be right."
Let's say you are working with a dataset in Access that has a date field called Insp Date. The table could be dams or rental property. You decide you want to see all the dams that were inspected in 1995. You could, for any number of reasons, type a query like this:
SELECT * FROM Dams WHERE InspDate = 1995
Or, if you are using Design view, you typed 1995 in the criteria grid under the InspDate column.
Because it has no syntax errors, the query would run. Most likely, however, your result would have zero rows, which you would think couldn't be right because you know you saw some 1995 dates when browsing the data. If you happened to be seeking inspections after 1995, so that the criteria was > 1995, you most likely would get a result that included every row in the table. Again, you'd think, this can't be right.
You get what you ask for
Here's the real problem. Access gave you just what you asked for; you just didn't know why or how.
At this point, a FoxPro user might point out that the problem is a data-type mismatch. You didn't use a date in the criteria, they'd say. You used a number that represents only the year portion of a complete calendar date. It's a good answer, but it still doesn't help. How come you didn't get a type-mismatch error when you compared a number to a date? How come, if you used the design view, Access didn't put the pound signs around the criteria like it usually does? How come = 1995 got no rows and > 1995 got all the rows?
The answer to all these questions is that Access stores date/time information as a number. For dates in 1999, for example, the number is 36161 or higher. Specific times of day are stored internally as a fraction of the whole number for that day. So for the date we know on the calendar as January 11, 1999, Access stores this internally as 36171. For noon on that date, the number is 36171.5. For 11:33 that night, the number is 36171.98125. This is all internal. You can display the calendar equivalent of the date in your table and in queries in many different ways, from short (1/11/99) to long (January 11, 1999).
The number is based on the number of days elapsed since a certain base date. In Access 97, the base date, or Day Number One, is December 31, 1899. Dates before that base date are stored as negative numbers. Part of the explanation here is that Access is being flexible, allowing you to refer to date values by the conventional calendar representations or the underlying number.
This explains the answers to queries like InspDate = 1995 or InspDate > 1995. Like all software, Access doesn't know what you meant to ask, only what you actually asked. And it didn't give an error message because you tried to use a number as a criteria for a date column. It interpreted your criteria of 1995 not as the year 1995 but as the 1995th day since December 31, 1899, or June 17, 1905. If your data are apartment inspections, then probably none of them have a recorded inspection date that old, so your query would return zero rows. But most of them probably have a date later than 1905, so your query would return all the rows that have some date recorded. Both results were not what you expected, hence this long attempt to explain how to reconcile what you got with what you expected to get.
Unleashing built-in functions
According to a calendar I checked, the vernal equinox this year occurs March 20, 1999 at 8:46 PM, Eastern Standard Time. That's 36239.8652777778 in the Access date-storage scheme.
If you want to see more of how this works, try a query like this on a table that has a date column in it. SELECT InspDate, CDbl(InspDate) FROM Dams
The Access function, CDbl converts whatever is inside the parentheses to a double-precision number.
Here are two solutions for what to do when you meant to ask for all the dams inspected some time in 1995. The first one involves remembering to use full dates - not just the year - in the criteria.
SELECT * FROM Dams WHERE InspDate between #1/1/1995# and #12/31/1995#
The other solution involves taking advantage of built-in functions in Access (and FoxPro) that let you pluck out certain parts of a calendar date.
SELECT * FROM Dams WHERE year(InspDate) = 1995
To find all the dams inspected in March:
SELECT * FROM Dams WHERE month(InspDate) = 3
To find all the dams inspected on the 5th of the month:
SELECT * FROM Dams WHERE day(InspDate) = 5
To find all the dams inspected on Monday: SELECT * FROM Dams WHERE DayOfWeek(InspDate) = 2
All of these functions look at the date and return a number that describes the component of the date you asked for: the number of the year; the number of the month (between 1 and 12); the number of the day (between 1 and 31); or the number of the day of the week (between 1 and 7). For day of the week, Sunday is 1 by default. This can be changed.
Knowing your software is not just technical trivia or the mastering of minutiae to impress people at work. Eventually, doing your journalistic duty to know exactly how and why you got a certain answer will require you to look behind the curtain.
Richard Mullins can be reached by e-mail at richard@nicar.org