Date Handling

The way Access stores and manipulates dates can be a source of confusion to developers, especially those who remember the older database methods of storing days, months, and years in date fields. Access handles dates in an elegant, easy-to-use way.

How Access Stores Dates and Times

Access stores a particular date as the number of days that have elapsed since an arbitrary starting "zero date" (which happens to be December 30, 1899). You can prove this to yourself by typing the following in the Immediate Window (you can bring up the Immediate Window in Access using Ctrl-G).

The CLng function converts an expression to a Long Integer. To this question, Access will answer with 1, meaning that 1 day elapsed since December 30, 1899. Of course, Access can handle dates before this date; they're stored as negative integers. If you want to see how many days have elapsed since that special zero date, try this:

?CLng(Date)

Access can perform date math very easily, because internally it doesn't store a date as days, months, and years. It just stores the number of days since the zero date and converts that value to an understandable date format only when the date needs to be displayed. But the date storage technique that Access uses goes even farther. Access can also store the time of day in the same date field. To do this, Access uses the decimal portion (the numbers after the decimal point) to store a fraction of a day. For example, 12 noon is stored as .5 (half way through the day), and 6 a.m. is stored as .2 5. Again, you can see this for yourself by typing this into the Immediate Window:

?CDbl(Now)

Notice a couple of things about this example to see the internal date and time value. One is that you now need to use CDbl (Convert to Double Precision Number) so that you can see the decimal portion (the time portion) that is returned by the Now function. Also notice that each time you run this command, you'll see that the decimal portion changes, because time is elapsing.

When you are storing the current date in a table, be sure to use the Date function. If you use Now, you'll also get a time component, which may cause incorrect results when you use dates in your query criteria. For example, if your query selects records where a date field is < = 4/28/2003, then any records with a date of 4/28/2003 should be returned. However, if they were stored with a decimal time component (by using Now instead of Date), they'll be fractionally greater than 4/28/2003 and won't be returned.

Simple Date Math

To add or subtract calendar time from a date field, use the DateAdd function. For example, to add 1 month to today's date, use

?dateadd("m",1,Date)

To subtract, use a negative number for the second parameter, Number. You can use different units of calendar time for the Interval parameter, like "d" for days, "ww" for weeks, "q" for quarters, and so on. Be careful when adding or subtracting years; you have to use "yyyy", not just "y". The Interval of "y" is Day of Year, which acts just like Day in the DateAdd function.

Date Math Example

Here's an example of date math that you can use. It computes the last day of a month by finding the first day of the next month, then subtracting 1 day.

Public Function LastDateofMonth(StartDate As Date) On Error GoTo Error_Handler Dim dtNextMonth As Date Dim dtNewDate As Date

'add a month to the start date

dtNextMonth = DateAdd("m", 1, StartDate)

'build a date

dtNewDate = CDate((DatePart("m", dtNextMonth)) & _

"/01/" & (DatePart("yyyy"/ dtNextMonth)))

'subtract a day

LastDateofMonth = dtNewDate - 1

Exit_Procedure:

Exit Function

Error_Handler:

MsgBox "An error has occurred in this application. " _

& "Please contact your technical support person and tell "

& "them this information:" _

& vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " _

& Err.Description, _

Buttons:=vbCritical, title:="My Application"

Resume Exit_Procedure

Resume

End Function

Note the use of Cdate, which converts any expression that can be interpreted as a date into an actual date data type. You can use the IsDate to check whether an expression can be interpreted as a date. Also note how the DatePart function is used to break up a date into string components for Month, Year, and so on.

0 0

Responses

  • alanna
    Why does VBA use 12/30/1899 for zero?
    7 years ago

Post a comment