From the course: Access 2016: Queries

Dates as serial numbers

From the course: Access 2016: Queries

Start my 1-month free trial Buy for my team

Dates as serial numbers

“

- [Voiceover] You would think that storing dates in your database would be a fairly straightforward and routine operation. Luckily on the surface, to you and I, it is. But behind the scenes, "Access" does a lot of work to make sure that your date specific records are kept in order. Understanding how "Access" stores, retrieves, and formats date and time based data can really help you out down the road, and there's lots of functions built in that deal specifically with dates and times. To get a handle on how "Access" is storing dates in the database, let's take a look at two tables that I've built for you. I'm gonna open up this one here called "Dates," and you can see I have a single column here, just of date values. They range from the 1700s, and if I scroll all the way down, you'll find some dates here in the present era, all the way up to today's date that I'm recording this course of 4/12/2016. Let's go ahead and close this one down, and take a look at the "Times" table. Here I just have a listing of times, and most of them are to the hour. If you scroll down here to the bottom though, you'll find some that are to the minute, and even down to the second. So let's go ahead and close this table, and let's take a look at a couple of "Queries." I'm gonna right-click on this one called "Date Serial," and open it straight into "Design View." Now the first column in this Query is just the date data coming out of the table. The second column here though is using a function called "CDBL" applied to our data. The "CDBL" or "C Double Function," is gonna take our date and it's going to reveal to us the serial number that "Access" is using in the background to store our dates. If I run this Query, you'll see what I mean. Here in the first column, I've got our dates, and here in the second column, I've got the serial number representation of that date. So today's date, for me, is "42,472" as a serial number. If you go down to the 1960s, you'll see that we're in the 22,000 range, the 1940s we're in the 17,000 range, and so on, all the way down 'til you get to day number "zero," which is December 30, 1899. This is the date at which "Access" starts counting days in its database. The next day, December 31, 1899, gets the day serial number of "one," and the following day, which is January 1st, or New Year's Day 1900, gets a serial number of "two." If you go before the "zero" day, back to 12/29/1899, you'll get a "negative one," and you'll see that as you get further away from that date in 1899, that your serial number representation gets increasingly negative. So date based serial numbers in "Access" are stored by a whole number representation. Let's take a look at how times are stored. This Query uses the same function, let's go ahead and double-click on it in order to reveal its data, and here we have our time data, and the serial number representation of the time. We can see that at 12 midnight, it gets the representation of "0.00." One second after midnight, we're at the serial number of ".000012." Halfway through the day would be 12 p.m., or noon, and you can see that gets the representation of "point five," so that makes sense. A quarter of the way through the day at six a.m., is ".25." Three quarters of the way through the day at six p.m., is ".75" And you can see how this tracks all the down until you get to "11:59 and 59 seconds" which is represented by fraction ".999988." So behind the scenes "Access" is storing dates as a whole number and times as a fraction. We can make use of this in mathematical operations. Let's go ahead and close this out, and create a new Query in "Design" view. I'll just go ahead and add in the "Dates" table and close the "Show Table" window. If I wanted to find the number of days between two specific dates, all I need to do is subtract one from the other. Let's see how that works. I'm gonna right-click in this first field, and go into the "Zoom" box. And I'll just change the font size up a bit. Now what I wanna do is find the number of days until Mother's Day from the day that it is right now for me. I'm gonna type in the date of 4/12/2016, and I'm gonna subtract from that the date of Mother's Day, which is 5/8/2016. Now if I were to run this right now, "Access" might think that I'm trying to take the number of "four," divide it by "12," divide that by "2016," minus "five," divide it by "eight," and divide it by "2016" again. In order to make sure that "Access" understands these as dates, we need to wrap them in something called a "Date Delimiter." These function just like the quotation marks that you would wrap around text. The "Date Delimiter" is the pound sign, which is "Shift + 3" on your standard U.S. keyboard. I'm gonna wrap a first date here with the pound symbols around each side, and I'm gonna do the same thing with the second date here. By doing this, I make sure that "Access" understands that I mean the date April 12th 2016 and May 8th 2016. Let's go ahead and say, "OK" to this, and we'll run the Query. This tells me that there are 26 days from now until Mother's Day. We can also use simple mathematical operations to find a day a certain number of days away from today. Let's go back into "Design" view, and I'll right-click here and go back into our "Zoom" box. Instead of finding the distance to Mother's Day, let's find out what day it was three days ago. All I need to do here is say "today's date minus three." Let's go ahead and say "OK" to that, we'll run it again, and we'll see the date three days ago was April 9th. By using these serial numbers, "Access" makes these kinds of computations really easy. Understanding the serial number nature of how "Access" stores dates can clear up some evaluations down the road. It's always a good idea to understand what's going on behind the scenes in order to make better sense of how we can work with that on the front end.

Contents