Microsoft Access Tips for Serious Users

Provided by Allen Browne, allenbrowne.com. Updated December 2008.

International Dates in Access

Americans format dates mm/dd/yy. British commonwealth countries use dd/mm/yy. Koreans use yy/mm/dd. In some countries, the date separators are dots or dashes, not slashes.

If you are outside the USA, or developing applications that may be used in other countries, you must know to handle dates in Access.

When you type a date into Access, your entry is stored as a number, where the integer part represents the date and the fraction part the time (part of a day). Things can go wrong at four points:

1. Access misinterprets a date entry.
2. You fail to format dates correctly in code.
3. Access does not know an entry is a date.
4. You use WeekdayName().

For information on date standards beyond Access, see ISO 8601. For details on how to get the user's regional settings, see Michael Kaplan's basIntlFormats code.

1. Misinterpretation in the User Interface

Define the date format for your locale in Windows Control Panel | Regional Settings. You can therefore use your local date format when you enter dates into the user interface part of Access: tables, queries, forms, or the Criteria of Query Design View.

Unfortunately, Microsoft tried to be too smart at helping Access accept dates. If you enter a date that is invalid for your local settings, Access spins the date around trying to find an interpretation that works. For example, with British dates in Control Panel, if you enter 10/13/01, Access realises there is no 13th month, and decides you must have intended 13-Oct-01. The results can be bizarre. The entry 02/29/01 should generate an error message that 2001 is not a leap year. It doesn't. Instead, Access plays with the entry and decides you must have intended Feb-1-2029 !!!

Aside from this madness (which cannot be turned off), just remember the user interface in Access uses the local Control Panel settings to interpret dates typed into the user interface.

2. Wrong Formatting in Code

In VBA code, delimit dates with the "#" symbol. Regardless or your regional settings, Access expects these literal dates to be in the American format, e.g. #12/31/1999#.

To demonstrate this, enter any date in the Criteria row under a date field in Query Design, and then switch to SQL View. In Query Design view, you see the date according to your local settings, but the SQL statement uses mm/dd/yyyy format.

SQL clauses are not always obvious, e.g. the Filter of a form, the WhereCondition of OpenReport, or the third argument of a domain aggregate function. Examples:

• DoCmd.OpenReport "MyReport", acViewPreview, , "InvoiceDate > #12/31/2000#"
• Debug.Print DLookup("StudentID", "tblStudent", "EntryDate = #6/30/1953#")
• strSQL = "SELECT * FROM tblDonation WHERE DonationDate > #" & Format(Me.StartDate, "mm\/dd\/yyyy") & "#;"

The third example demonstrates how to concatenate a date into a SQL string. The Format() function is essential to force the date into American format. Unfortunately, Format() replaces the slashes with the date separator character defined in Control Panel | Regional Settings, so you must specify literal slashes in the format string by preceding the slash with backslashes.

Since this is something you do frequently, you may find it easy to call a small wrapper function for concatenating date strings. The example below formats it as:

Function SQLDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne., June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

3. Data Type not Recognised

There are two cases where Access may not understand you intend an entry as a date.

Unbound Controls

Text boxes bound to Date fields are not a problem, but if the control is unbound (nothing in its Control Source property), how can Access know what data type you intend? If you are in the USA, it will probably guess correctly. If you use another date format, there's a high probability that your date will be interpreted wrongly.

In general, the Format property of the control has to do with how Access displays data and does not control data entry. However, if you set the Format property to "Short Date" or similar, Access is unable format an invalid entry, so only valid dates are accepted. And since Access now knows the data type, the erroneous interpretations of the date also cease.

As simple as it sounds, setting the Format property is enough to coerce Access into recognizing the data type for unbound controls.

Calculated Date Fields

Access can also misinterpret calculated date fields in queries, especially where the date format is not American and the field contains some Nulls. The obvious symptoms are that the field is left-aligned and sorted as strings.

The solution is to explicitly typecast all calculated date fields, e.g.:

    DueDate: CVDate([InvoiceDate] + 30)

(Note: CDate() fails on Null values, so CVDate() has more uses than the "compatibility" issue highlighted in the Access documentation.)

With these few simple tricks, you can create databases that can be safely used anywhere, even if the user's date formats are different from the developer's.

4. WeekdayName() gives inconsistent results

Avoid the WeekdayName() function. Instead, use Format([MyDate], "dddd").

Format() and Weekday() are no problem. They use vbSunday as the default value for the first day of the week. But WeekdayName() defaults to vbUseSystemDayOfWeek. The results from WeekdayName() therefore change, depending on your the settings for your locale.

In the Windows Registry under HKCU\Control Panel\International, are settings for iFirstDayOfWeek and iFirstWeekOfYear. The system day values change depending on settings, and are zero-based (0 to 6), whereas the day names have values 1 to 7 in VBA.

That means you do not get a consistent answer from:
    ? WeekdayName(1)
Consequently, if you rely on the default behavior of WeekdayName(), your code will break when your database is used on a computer that uses different regional settings. For more detail about these settings, see: http://msdn.microsoft.com/en-us/library/bb507201.aspx

Given that:

• WeekdayName() does not work in earlier versions,
• Access 2000 has problems with it as well,
• The Format() function works in all versions and all regions,

you are better off using Format() instead.