Roger's Access Blog

Now() Function in Default Value Stores the Wrong Time

2012-01-25T06:41:00.001-05:00

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

2012-01-24T09:08:00.000-05:00

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

2012-01-20T07:30:00.001-05:00

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.

Date Stuff – Functions
Date Stuff – Using the functions
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")