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