The Access Wizard Newsletter Tips Tricks and Traps for Access Users and Developers
January 2007

Date Math 101 - Storage

Thirty days hath September,
April, June, and November
All the rest have 31, though February's underdone
With 28--hold the line! -- Leap Day makes it 29.


Thirty days hath September,
All the rest I can't remember.
The calendar hangs on the wall;
Why bother me with this at all?

Date math gives me a headache. The rules are remarkably complex. For example, most of us know that leap year occurs every 4 years when the year is divisible by 4-but there are exceptions to that rule And if you're in business, you probably care about fiscal months, which are usually, but not always, 28 days. If you’re setting up a database to do something one month after April 30th, does that mean May 30 or May 31? These are all questions that will likely cause a database programmer to scratch his or her head.

When you start trying to do math in the aforementioned (and other) situations, life becomes even more complex. We all know that a year is typically 365 days or 52 weeks long. Each week contains 7 days. Have you ever multiplied 7 x 52? It comes out to 364. So a typical year contains 52.14286 weeks (give or take).

This month we start a series on date math. My goal is to reduce the complexity so that when you're faced with date math, you can break out some tools and do the calculations without having to sweat the details. As usual, we’ll start at the beginning.

In this Issue
  • Tip of the Month – Save a Record on the Fly
  • How Dates are Stored
  • The Problem (and Solution) Related to Retrieving Stored Dates
  • Conclusion and the Road Ahead
  • Reader Feedback and a Correction

  • How Dates are Stored

    When you declare a field in a Microsoft Access table, one of your options for field type is Date/Time. Note that the option is not simply Date, it's Date AND Time. What you can infer from this is that when you store a date, you also store a time whether you want to or not. Behind the scenes, dates are not stored in the format of 1/17/07 or even 1-17-2007, but rather as floating point numbers (think really, really precise numbers). Their calculation is based on a reference date arbitrarily selected by Microsoft of midnight, December 30, 1899.

    So January 17, 2007 is actually stored as 39099. January 18, 2007 is stored as 39100 and so on. Time is stored as the decimal part of a day. Midnight is the beginning of the day, so 12 noon is equal to .5. Each hour equates to about .041667 of the day; each minute is worth .000694; and each second equals .0000115741 of a day.

    Looked at from the other direction, each 1/10 of a day represents 2 hours and 24 minutes.

    You can get ridiculously precise about these things, much more than we typically need to in Access. It's nice to know though that if you have need for very accurate time measurement in your Access Application, you can get it.

    The Problem (and Solution) Related to Retrieving Stored Dates

    Since dates are stored as floating point numbers with time represented as a decimal, if you enter a date field using the function Now(), you'll be storing a number with decimal points. If you ask Access for that date back, you'll get the date as well as the time, e.g., 1/17/2007 2:24:00 AM.

    But what if you only care about the date segment of the value, 1/17/07? The easiest way to approach this is to make sure you store only the date. In common usage this is easy enough because dates are typically entered into an Access form as something like 1/17/07 and Access will store that as a number with no decimal.

    But let's say that you automatically populate the date field using the Now function, which embeds a time element in your date field. What the best way to isolate the date without the time? The simplest approach is to lop off everything to the right of the decimal point. You can do this by using the function Int ().

    For instance if your date is stored in a field called DateOfOrder as 1/17/2007 2:24:00 AM (39099.1), you can ask for Int(DateOfOrder) to retrieve only the date. Using the function Int will return 39099 rather than 39099.1.

    If you're showing this on a form or report that displays a date, you'll be all set. If you need to change the format of the date, you can use the format function to modify the display (more on the format function in future issue)

    Conclusion and the Road Ahead

    We've just begun the journey of dates and times. We've begun to appreciate the complexity of date math, and how dates and times are stored. As we move forward in time :) we'll look further into the complexity of dates and begin to wrestle with date calculations.

    Reader Feedback and a Correction

    Last month in discussing the use of Macros I said that one of the disadvantages is that Access macros didn't allow conditional logic (if this situation occurs do X, otherwise do Y)

    Shortly after the last issue came out, I heard from reader Scott Rockhill:

    In this month’s letter, you address macros, and how they can be used as a step for getting into coding. The first thing on your list of “bad” is lack of conditional logic. I may misunderstand you, but I disagree with that statement. I use conditional logic on many of the macros I build. The “Condition” column just to the left of the “Action” column appears if you tell it to. It is there that I decide what criteria needs to be met for a particular line of the macro to run. I can then put the alternate part of the criteria below it to run another line. It is extremely useful and means my end-users can make adjustments without VBA knowledge.

    Well, it turns out that Scott is right. You can use conditional logic in macros. It's a hidden feature that I wasn't aware of. To turn on this feature you have to change an option in Access. From the main Access menu, choose Tools | Options | Views. In the Show in Macros Design section there is an option called "conditions column." If you check this box then your macro design window will show an additional column that will let you apply a condition to an action.

    Thanks for the correction Scott!

    Tip of the Month – Save a Record on the Fly

    I recently learned of a new technique to easily save a record on the fly. If your form shows a little pencil symbol as you are working on a record, that indicates that you are in the process of changing the data, but your changes haven't yet been saved.

    Typically a record is saved when you move to another record or close the form. However if you are doing a lot of work on a single record you may want to make sure that things are saved periodically while you're still working on the record. In order to do this, simply click on the pencil icon (or anywhere in that area of the record selector) and the record is immediately saved.

    As an alternative, if you prefer the keyboard to the mouse, you can save the record by holding down the Shift key and pressing the Enter key.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services

    Join our mailing list!

    Forward email

    This email was sent to jim@custom-software.biz, by jim@custom-software.biz

    Custom Software | Copyright Custom Software All Rights Reserved | Westford | MA | 01886