Now() Function in Default Value Stores the Wrong Time

In a previous post (Date Stuff – Using the functions), I discussed, among other things, using built in Access functions (specifically Date() and Now()) in the default value of a field in a table.

The Date() function is generally safe to use, but the Now() function is problematic. Now() in the default value of your field WILL store the wrong time. If your concern is to store an accurate time of when the record was saved, this is a problem.

Let me illustrate.  Suppose I have a simple table like so:

Now, I'm going to add a field called CreatedDate, and use the Now() function in the Default Value of the field.

The New Record, that is, the one at the bottom with the asterisk at the far left, has not yet been created.  The date and time displayed is the time that I opened the table (or form, if the form was based on that table). 

However, suppose I take a few minutes before I actually enter anything into the new record.

Once I begin typing in the record, it is created (but not yet saved), but notice that the time that I opened the table is still displayed, even though the actual time (as shown in the task bar) is 6 minutes later.

By the time I get around to actually saving the record (by moving to the new New Record) another 5 minutes has passed.

So the record was saved at 6:29, but the time stored in the field is 6:18.  Not only that, but the next New Record displays the time that the previous record was created.

Solutions

So what do you do to get an accurate time in the CreatedDate field?  There are two solutions depending on which version of Access you're using: Access 2007 (and previous) or Access 2010.

Access 2007 and Previous

In Access 2007 and before, it cannot be done at the table level.  It must be done in a form.  So in Design View of your form, create a code procedure in the BeforeUpdate Event of the form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull(CreatedDate) Then
        CreatedDate = Now()
    End If
End Sub

Access 2010

With the introduction of Access 2010, we how have Data Macros (essentially triggers) that work at the table level.

On the Tables tab, select the Before Change Event and create the following data macro:

Now, you will save the actual time the record was saved.

New Sample: Report_TrainControlChart

by AD Tejpal

This sample db demonstrates time distance charting of trains on a double line route. Time axis, covering 24 hours is depicted horizontally, while various railway stations are displayed along the vertical axis. Vertical lines across chart area identify half hour time intervals. For convenient legibility, time lines at two hour intervals have darker shade.

Positioning of stations is in proportion to their respective distance from terminal station as compared to overall length of route. Major stations have adequate loop lines, while the minor ones have no facility for overtaking.

Each pair of trains is assigned a distinct color (in table T_Trains). T1/T2 (Red) is a slow train, stopping at all stations. T3/T4 (Blue) is faster and stops only at major stations en-route. T5/T6 (Green) is a super fast pair, stopping at only at some of the major stations en-route. Halts of slower trains are devised in such a manner as to permit un-impeded overtake by faster ones. Two local trains (L1 and L2) performing multiple U trips are also demonstrated. Start and end points of each train have been made prominent by placement of a marker (a small circle - looks like a big dot) in a color matching that of the train in question.

Train control chart can be viewed in three alternative styles as follows, depending upon user's choice via option group:

(a) Whole route at a glance.

(b) Zoomed view of first half of route.

(c) Zoomed view of second half of route.

Note:

(a) If more stations get added (in table T_Stations), proportionate inter-station spacing in the chart will get adjusted automatically so as to suit the available chart height.

(b) Chart height is set as 8100 twips via report level constant mTotChartHt. This is found to work fine in Access 2003 on Win XP (Paper size: A4, Default printer: HP Laserjet M1005). If, depending upon local set up, it is found that the report tends to spill over beyond one page, the value of this constant can be adjusted downwards suitably. For complete chart to be depicted, it is important that report (R_TrainControlChart) remains confined to a single page.

Version: Access 2000 file format.   References: DAO 3.6

You can find the sample here: http://www.rogersaccesslibrary.com/forum/topic581_post599.html

.

Date Stuff – Useful Examples

This post is the third in a 3-part series on Date functions in Access. If you have not read the first two posts, it might be worth while to do so.

1. Date Stuff – Functions
2. Date Stuff – Using the functions
3. Date Stuff – Useful Examples (this post)

To see working examples, please see the following sample: DateStuff.mdb on my website: www.RogersAccessLibrary.com.

Useful Examples

As with most things in Access, there are multiple ways to do most each of the things below, so I'll list at least a couple of methods.  There may be more.

First Day of the Current Month

• DateSerial(DatePart("yyyy", Date()), DatePart("m", Date()),1)
• DateAdd("d",1-DatePart("d",Date()),Date())
• CDate(CStr(DatePart("m",Date())) & "/1")

Used in a UDF (User Defined Function). Any of the above formulas can be substituted:

Function FirstofMonth(vdate As Date)
FirstofMonth = DateAdd("d",1-DatePart("d",Date()),Date())
End Function

Last Day of the Current Month

• DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())+1,1)-1
• DateAdd("m",1,CDate(CStr(DatePart("m",Date())) & "/1"))-1

Used in a UDF (User Defined Function). Any of the above formulas can be substituted:

Function LastofMonth(vdate As Date)
LastofMonth =DateAdd("m",1,CDate(CStr(DatePart("m",Date())) & "/1"))-1
End Function

First Day of the Previous Month

• DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())-1,1)
• DateAdd("m",-1,CDate(CStr(DatePart("m",Date())) & "/1"))

Used in a UDF (User Defined Function). Any of the above formulas can be substituted:

Function FirstofPrevMonth(vdate As Date)
FirstofPrevMonth=DateAdd("d",1-DatePart("d",Date()),Date()) – 1
End Function

Last Day of the Previous Month

• DateSerial(DatePart("yyyy", Date()), DatePart("m",Date()),1)-1
• DateAdd("d",1-DatePart("d",Date()),Date()) - 1
• CDate(CStr(DatePart("m",Date())) & "/1") – 1

Used in a UDF (User Defined Function). Any of the above formulas can be substituted:

Function LastofPrevMonth(vdate As Date)
LastofPrevMonth=DateAdd("d",1-DatePart("d",Date()),Date())–1
End Function

First Day of the Next Month

• DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())+1,1)
• DateAdd("m",1,CDate(CStr(DatePart("m",Date())) & "/1"))

Used in a UDF (User Defined Function). Any of the above formulas can be substituted:

Function FirstofNextMonth(vdate As Date)
FirstofNextMonth = DateAdd("m",1,CDate(CStr(DatePart("m",Date()))& "/1"))
End Function

Last Day of the Next Month

• DateSerial(DatePart("yyyy",Date()),DatePart("m",Date())+2,1)-1
• DateAdd("m",1,CDate(CStr(DatePart("m",Date())+1) & "/1")-1)

Used in a UDF (User Defined Function). Any of the above formulas can be substituted:

Function LastofNextMonth(vdate As Date)
LastofNextMonth = DateAdd("m",1,CDate(CStr(DatePart("m",Date())+1) & "/1")-1)
End Function

First Day of This Week

• DateDiff("d",Weekday(Date()-1,1),Date())
• Date()-Weekday(Date())+1

Used in a UDF (User Defined Function). Any of the above formulas can be substituted:

Function FirstofWeek(vdate As Date)
FirstofWeek= DateDiff("d",Weekday(Date()-1,1),Date()) 
End Function

Last Day of This Week

• DateDiff("d",Weekday(Date()-1,1),Date())+6
• Date()+(7-Weekday(Date()))

Used in a UDF (User Defined Function). Any of the above formulas can be substituted:

Function LastOfWeek(vdate As Date)
LastOfWeek= DateDiff("d",Weekday(Date()-1,1),Date())+6
End Function

First Work Day of This Week

• DateDiff("d",Weekday(Date()-1,2),Date())
• Date()-Weekday(Date())+2

Used in a UDF (User Defined Function). Any of the above formulas can be substituted:

Function FirstWorkdayOfWeek(vdate As Date)
FirstWorkdayOfWeek= DateDiff("d",Weekday(Date()-1,2),Date())
End Function

Last Work Day of This Week

• DateDiff("d",Weekday(Date()-1,2),Date())+4
• Date()+(6-Weekday(Date()))

Used in a UDF (User Defined Function). Any of the above formulas can be substituted:

Function LastWorkdayOfWeek(vdate As Date)
LastWorkdayOfWeek= DateDiff("d",Weekday(Date()-1,2),Date())+4 
End Function

Number of Week Days

This function will calculate the number of week days between any two dates.

Function NumberOfWeekdays(begindate As Date,
                 EndDate As Date,
                 bolInclusive As Boolean) As Integer

Dim intCounter As Integer
Dim intMovingDate As Date
intCounter = 0
If bolInclusive Then
    intMovingDate = begindate
    EndDate = EndDate + 1
Else
    intMovingDate = begindate + 1
End If
Do While intMovingDate < EndDate
    If WeekDay(intMovingDate) <> 1 And WeekDay(intMovingDate) <> 7 Then
        intCounter = intCounter + 1
    End If
        intMovingDate = intMovingDate + 1
    Loop
NumberOfWeekdays = intCounter
End Function

Calling the function. (The third argument determines whether you want to include the begin and end dates in the weekday count.)

Sub testweekdays()
'uses the FirstOfMonth function above. Literal dates like #1/1/2012# can also be used.
Dim intdays As Integer
    intdays = NumberOfWeekdays(FirstofMonth(Date), Date, True)
    MsgBox intdays
End Sub

 

 

 

 

Date Stuff–Using the Functions

This post is the second in a 3-part series on Date functions in Access. If you have not read the first post (Date Stuff – Functions), it might be worth while to do so.

1. Date Stuff – Functions
2. Date Stuff – Using the functions (this post)
3. Date Stuff – Useful Examples

To see working examples, please see the following sample: DateStuff.mdb on my website: www.RogersAccessLibrary.com.

Using Date Functions

Date functions (as indeed any built in or user defined functions) can be used in several ways within the database application:

• Control Source
• Queries
• UDF (User Defined Functions)
• Default Value of Field

Date Functions in the Control Source

Perhaps the easiest way to use date functions is directly in the ControlSource property of a form or report control.  What is a control?  Control is a general name for objects on forms or reports like textboxes, comboboxes, listboxes, buttons, and so forth.  Most of the time, this is going to be a textbox,

The ControlSource property determines where the control (textbox) will get it's value.  On a "bound" control, this is usually a field from the underlying RecordSource (table or query) of the form or report.  However, controls can also be "unbound", which means it is not tied to a particular field.  In this case, you can use the Control Source to display any number of things.  An unbound control will ONLY display values.  They won't be saved to the underlying table.

To use a date function in the ControlSource of a textbox, simply preface it with an equal sign (=), much like you do with a function in an Excel cell. 

For instance to display the current date in a textbox:

=Date()

To display the someone's age, you can use the DateDiff function to find the difference between the current date and their birth date which would have to be stored in a field like DOB.

=DateDiff("yyyy",[DOB],Date())

Notice that when the Date() function is used, you MUST use the parentheses after it.  If you don't, Access will assume it's a field named [Date] and it will produce an error.

Date Functions in Queries

Instead of calculating the date at the form level, you can also use the date functions in the query underlying the form or report.

To do the same age calculation in the Query Builder, I could add a calculated field like this:

Age: DateDiff("yyyy", [DOB], Date())

In SQL View, it would look like this:

SELECT FirstName, LastName, DOB, DateDiff("yyyy",[DOB],Date()) AS Age
FROM BirthDates;

Now, suppose instead of just listing everyone's ages, I want to select just those records for people between 12 and 45.  I can add a criteria to my calculated field like this:

SELECT FirstName, LastName, DOB, DateDiff("yyyy",[DOB],Date()) AS Age
FROM BirthDates
WHERE DateDiff("yyyy",[DOB],Date()) Between 12 And 45;

Of course, that's not the only way to do this.  I could also use the DateAdd function in the criteria of DOB, like this:

SELECT FirstName, LastName, DOB
FROM BirthDates
WHERE DOB Between DateAdd("yyyy",-12,Date()) And DateAdd("yyyy",-45,Date())

Here, I'm using the DateAdd with a negative value to subtract 12 and 45 years from the current date to yield the correct date range.

Date Functions in User Defined Functions (UDF)

Date functions can also be used in VBA code. If I use a particular date conversion frequently, I can create a User Defined Function (UDF) to calculate the value, which saves me from typing in the formula over and over.

For instance, if I need to calculate the age in many places, I could create the following function:

Function Age(DOB As Date) As String
    Age = (DateDiff("yyyy", [DOB], Date))
End Function

Notice that the current date function Date() is NOT followed by parentheses as it is in the ControlSource or in a Query.

This function can then be called from the ControlSource of a textbox:

or in a Query:

SELECT FirstName, LastName, DOB, age([DOB]) AS AgeInYears
FROM BirthDates;

In order to be used globally, that is, throughout the application, this function must be in a General Module rather than a module behind a form.  If it is in a module behind a form, it can ONLY be used in that form.

Date () in Default Value of Field

Lastly, you can use date functions in the Default Value of a field.  This can be useful if you want to enter a value when the record is created.  For instance, if you want the date and time the record was created, you can put =Date() in the Default Value property of a field.

Which will result in a the current date and time in the new record:

It is worth noting, however that this records the date and time that the record was CREATED, not when it was SAVED.  If the difference is important, you need to use a different method to calculate the date and time.  When using the Date() function, you usually don't have any problem.  However, the Now() function WILL store the wrong value for the time.  I'll discuss this more fully in a later post.

Similarly, if I wanted to create a date 30 days from the time the record is created, I could add:

=DateAdd("d",30,Date())

to the Default Value of the Net30 field:

There are some limitations, however. 

You can't use a User Defined Function in the Default Value property.  They can only be the built-in functions. You also can't reference another field in the table.

So the following WILL NOT work in the Default Value:

=DateDiff("yyyy", [DOB], Date)
=Age([DOB])

Next time, in Date Stuff – Useful Examples, I'll show some examples of date functions to do useful things like: 1) finding the first and last days of the current, previous, and next weeks; the first and last days of the current, previous, and next months; and so forth.

Date Stuff–Functions

This post is the first in a 3-part series on Date functions in Access.

1. Date Stuff – Functions (this post)
2. Date Stuff – Using the functions
3. Date Stuff – Useful Examples

To see working examples, please see the following sample: DateStuff.mdb on my website: www.RogersAccessLibrary.com.

Date Functions

Microsoft Access has a variety of date functions to help you manipulate date values. Because there are so many functions, there are often multiple ways to do the same thing.  For instance, if I wanted to find the first day of this month, I could do:

DateAdd("d",1-DatePart("d",Date()),Date())
or
CDate(CStr(DatePart("m",Date())) & "/1")

And there are probably other ways as well. The purpose of this post is to explain how dates work and a few of the functions used to manipulate them.

What Are Dates?

First of all, in Access a date field stores both the date and time values as a double-precision number, that is, a whole number and decimal.  For instance, right this second, the date/time is 1/5/2012 6:53:04 AM. This value is actually stored as 40913.28686. 

The whole number (40913) represents the date (1/5/2012).  This is the number of days since 1/1/1900.  The decimal (0.28686) is the fractional part of the day and represents the time (6:53:04 AM).

The implications of this is that if I want to add a day to a date value, I can simply add 1.  If I want to subtract 12 hours from a time, I can simply subtract 0.5.  This is all well and good if I am dealing only with days.  But if we want to add a month to, or find someone's age in years or months, or add add 10 work days to a date, it becomes much more complicated.

This is where the built in date functions come in handy.  Used in combination, I can manipulate dates in terms of days, work days, weeks, months, and years; and times in hours, minutes, and seconds.

Date Functions

The some of the more useful date functions are:

1. Date() / Now()
2. DatePart()
3. DateSerial()
4. DateAdd()
5. DateDiff()
6. CDate()

Date() / Now()

The Date() function returns the current system date, that is, just the whole number part of the datetime (40913.0).  The Now() function returns the current date AND time, that is, both the whole number and decimal parts (40913.28686).

This is very useful (as we saw above) in determining dates relative to today's date.  I'll be using Date() in the examples below, but any date could be substituted.

DatePart()

The DatePart function allows you to determine the value of a given date in terms of specific intervals.  In other words, you can determine the year, month, day, week, quarter, etc, of a given date.  The syntax looks like this:

DatePart(interval, date [, firstdayofweek] [, firstweekofyear] )

Intervals:

yyyy	Year
q	Quarter
m	Month
y	Day of year
d	Day
w	Weekday
ww	Week
h	Hour
n	Minute
s	Second

The following are some examples intervals returned using DatePart:

DatePart("yyyy", Date()) returns 2012.
DatePart("m", Date()) returns 1.
DatePart("d", Date()) returns 5.
DatePart("h", Now()) returns 6.

As you can see, DatePart can also be used to return times, but you need to use the Now() function because the Date() function doesn't return a time.

For more information, see the MS Knowledgebase article DatePart Function.

By the way, instead of the DatePart function, you can also use the Year(), Month(), Day() functions.  They work the same as the DatePart for each of their respective intervals.  I prefer to use DatePart because I can use it for everything.

DateSerial()

DateSerial(year, month, day)

The DateSerial function allows you to build a date value by giving values for the year, month, and day.  For instance, to build today's date, I could do this:

DateSerial(2012, 1, 5)

Of course, it's not all that useful when using literal values, but I can also use variable, which makes it extremely useful.  For instance, I could use it to create a date one year from today's date:

DateSerial(DatePart("yyyy", Date()), DatePart("m", Date()), DatePart("d", Date()))

So I can use the other date functions to build the separate intervals and then use the DateSerial to put them all together into a date value.

For more information, see the MS Knowledgebase article DateSerial Function.

DateAdd()

DateAdd(interval, number, date)

The DateAdd function allows you to add an interval to a given date.  The intervals are the same as those for the DatePart function, ie. year, month, day, week, etc. This is extremely useful because leap years and the different number of days in a month makes it difficult to accurately add some intervals by adding days.  The DateAdd function will accurately add intervals without any additional work on your part.

For instance, above, I built date one year from today with DateSerial.  However, I could do it much easier by using the DateAdd function:

DateAdd("yyyy", 1, Date())

A date six months is as easy as:

DateAdd("m", 6, Date())

For more information, see the MS Knowledgebase article DateAdd Function.

DateDiff()

DateDiff(interval, date1, date2 [, firstdayofweek] [, firstweekofyear] )

The DateDiff function allows you to find the interval between two dates.  The interval can be any of the standard intervals as seen in the DatePart function above, ie. year, month, day, week, etc.  This is the counterpart to the DateAdd function.

The DateDiff is really useful for determining age.  For instance, to find someone's age in years, I can to this (assumes DOB is a field holding the date of birth):

DateDiff("yyyy",[DOB],Now())

But I could just as easily find the age in months:

DateDiff("m",[DOB],Now())

For more information, see the MS Knowledgebase article DateDiff() function.

CDate()

The CDate() function is not a date function so much as a data conversion function.  You can use the CDate function to convert a string value into a Date.  The string has to be a recognizable date:

"January 2, 2012"

"12/1/2012"

You have to be a little careful if you don't use the US Standard date format of Month Day Year.  CDate will correctly interpret both "1 December, 2011" and December 1, 2011" as 12/1/2011.  But "12/1/2011" will evaluate to December 12 even if you meant it to be January 12.

While CDate is not a date function per se, it is sometimes useful for calculating certain date values.

For more information, see the MS Knowledgebase article: Conversion Functions.

Next time, I'll discuss various ways to use the Date Functions in Date Stuff – Using the functions.