Access Developer Needs a Date

<< Click to Display Table of Contents >>

Navigation:  Dates and Time >

Access Developer Needs a Date

Doug Steele        

In Access Answers, Doug Steele addresses commonly asked questions from Access developers. This month, he looks at problems dealing with date values.

I'm running into problems using dates, especially among users with varying regional settings. Users enter a date in one format in one region and get something different in another format. Am I storing my dates wrong?

This problem is common, and it can cause headaches when developing for users in different countries.

First, be aware that dates in Access aren't stored in a format that looks like a date to people like you and me (like, say, DD/MM/YY). Instead, the VBA date/time variable type is an IEEE 64-bit (8-byte) floating-point number. The integer part of the number represents the date as the number of days relative to 30 Dec, 1899. So, for instance, the date 04 Apr, 1897, is the number -1000, 29 Dec, 1899, is -1, 04 Jan, 1899, is +5, and 05 Jun, 2003, is +37777 (remember this number for later on). The decimal part of the number represents the time as a fraction of a day (for instance, 3:00 AM is .125, 8:00 AM is .3333333, noon is .5, 6:00 PM is .75, and so on).

It doesn't matter how the date was input. As long as your application interpreted the date correctly when it was input (more about this later), the date will be stored so that it can be used—regardless of the date format that the user wants to see it in. However, one area where you can have problems is if you haven't been providing a complete date when using a date/time field. If you've only been supplying a partial date, Access is going to do its best to "fill in the gaps" and provide you with a complete date. As an example, here's what you get with a date that consist of just two parts:

?Format("05/30", "dd mmm, yyyy")

30 May, 2003

?Format("10/2004", "dd mmm, yyyy")

01 Oct, 2004

The good news is that it's irrelevant what Regional Settings are in effect when the data is entered; the dates will be stored consistently in a format independent of any particular format.

How can I ensure that dates get into my database correctly?

Where things can go wrong is how you use dates in your application. There are essentially three options for literal dates:

• You can delimit the date with octothorpes (#).

• You can delimit the date with quotes.

• You can enter the number for the date (as discussed earlier).

Two points: Over the years, I've gotten into a number of arguments with people about whether "#" is the "pound symbol," the "hash symbol," the "sharp sign," or the "number sign." To avoid any further discussion, I'm going to refer to the # character as an octothorpe. See www.sigtel.com/tel_tech_octothorpe.html for more details. Additionally, for the purposes of simplification, I'm going to ignore time in the following discussion. What I'm describing doesn't change with the addition of time.

When you delimit your dates with octothorpes, it doesn't matter what the user locale settings have for Short Date format. Access is very specific about how it treats dates delimited with octothorpes. Assuming that the date is just numerics and slashes (such as 03/05/03), Access first assumes that the date is in mm/dd/(yy)yy format. Only if Access can't interpret the input as a date in that format is Access going to try using a different format for interpretation.

You can use formats other than numbers plus slashes with octothorpe-delimited dates—unambiguous settings like yyyy-mm-dd or dd mmm, (yy)yy will work as well. As you'll see, though, dd/mm/(yy)yy dates can't be used reliably with octothorpe-delimiters (and yes, I realize mm/dd/(yy)yy isn't unambiguous, but you have to remember that Access was developed in the US, where some assumptions about date formats are made). Notice that all of this processing is independent of the locale settings.

If you've been delimiting your dates with quotes, Access has been taking the string and converting it to a date using the locale settings of the current user. Therefore, your results will be affected by the locale settings in effect when the date was input.

The following examples should illustrate the differences between the three approaches. In this first set of examples, the Short Date format in the locale settings is set to yyyy-mm-dd. I've used the same date in two examples but delimited it differently to get different results. For the last entry, you'll need to remember that 37777 is the numeric representation of 05 Jun, 2003:

?Format(Date())

2003-05-31

?Format(#01/02/03#, "dd mmm, yyyy")

02 Jan, 2003

?Format("01/02/03", "dd mmm, yyyy")

03 Feb, 2001

?Format(37777, "dd mmm, yyyy")

05 Jun, 2003

For these next examples, I've set the Short Date format to mm/dd/yyyy (the same results will be obtained if the Short Date format is mm/dd/yy):

?Format(Date())

05/31/2003

?Format(#01/02/03#, "dd mmm, yyyy")

02 Jan, 2003

?Format("01/02/03", "dd mmm, yyyy")

02 Jan, 2003

?Format(37777, "dd mmm, yyyy")

05 Jun, 2003

Finally, for these examples the Short Date format is set to dd/mm/yyyy (the same results will be obtained if the Short Date format is dd/mm/yy):

?Format(Date())

31/05/2003

?Format(#01/02/03#, "dd mmm, yyyy")

02 Jan, 2003

?Format("01/02/03", "dd mmm, yyyy")

01 Feb, 2003

?Format(37777, "dd mmm, yyyy")

05 Jun, 2003

As you can see, the date is interpreted identically in all three cases when delimited with octothorpes, but differently in all three cases when delimited with quotes. And, as you might expect, using a number as a date causes the date to be treated consistently, regardless of the Short Date format (though you probably shouldn't count on your users entering dates as numbers).

What happens, though, if what you're passing isn't valid for the Short Date format that's in effect? The following examples show the difference when using 13/12/11 (which obviously isn't valid in mm/dd/yyyy format, since there's no 13th month). With the Short Date format set to yyyy-mm-dd, these are the results that you'll get:

?Format(Date())

2003-05-31

?Format(#13/12/11#, "dd mmm, yyyy")

11 Dec, 2013

?Format("13/12/11", "dd mmm, yyyy")

11 Dec, 2013

With the Short Date format set to mm/dd/yyyy the results are like this:

?Format(Date())

05/31/2003

?Format(#13/12/11#, "dd mmm, yyyy")

11 Dec, 2013

?Format("13/12/11", "dd mmm, yyyy")

11 Dec, 2013

And, finally, with the Short Date format set to dd/mm/yyyy:

?Format(Date())

31/05/2003

?Format(#13/12/11#, "dd mmm, yyyy")

11 Dec, 2013

?Format("13/12/11", "dd mmm, yyyy")

13 Dec, 2011

Again, in all three cases the date was interpreted consistently when delimited with octothorpes (it might be wrong, but it's consistent!). The date is interpreted differently when delimited with quotes.

So what's the advice I'm trying to give you? As I showed earlier, using octothorpes as delimiters is consistent, regardless of the user's locale settings.

I'll admit that the preceding examples were somewhat contrived because they don't use four-digit years. When you use four-digit years, the results are more consistent, since now Access only has to decide between month and day. Regardless of whether the Short Date format is yyyy-mm-dd, mm/dd/yyyy, or dd/mm/yyyy, you'll get the following results:

?Format(#13/12/2011#, "dd mmm, yyyy")

13 Dec, 2011

?Format("13/12/2011", "dd mmm, yyyy")

13 Dec, 2011

?Format(#12/13/2011#, "dd mmm, yyyy")

13 Dec, 2011

?Format("12/13/2011", "dd mmm, yyyy")

13 Dec, 2011

So there's your other tip: Use a four-digit year. I'm constantly surprised how many people don't use four-digit years. Wasn't there some noise in the press a few years ago about this?

Of course, if you can be confident that your dates are valid, then you needn't worry about incorrect interpretation. The only thing that matters is to present the dates to Access (or Jet, if you're running a query) in a way that Jet can handle them.

Since you're developing the application, you have complete control over which approach your code will use to represent dates. My recommendation is that you create a wrapper function to format all of your dates whenever you want to use them in VBA code. In this way, you can be assured that the user's locale settings won't matter.

The routine that I frequently use looks like this:

Function SQLDate(DateIn As Variant) As String

  If IsDate(DateIn) Then

    SQLDate = Format$(DateIn, "\#mm\/dd\/yyyy\#")

  End If

End Function

You could just as easily use this:

Function SQLDate(DateIn As Variant) As String

  If IsDate(DateIn) Then

    SQLDate = Format$(DateIn, "\#yyyy\-mm\-dd\#")

  End If

End Function

As you can see, I'm enclosing my dates in octothorpes and using a four-digit year. For the curious, the \ character that appears in my format strings is an escape character to ensure that what follows it is shown exactly as it appears. Since, in a formatting string, the # causes a digit to be inserted in the string, you must escape the # by preceding it with a \ to ensure that you get a # in your results and not a digit. Also, in formatting strings, the / character represents whatever date separator character has been set in the locale settings. So using the two slashes together (\/) ensures that you get / as the separator between the date parts. Without the escape, you'd get whatever the user has specified as the date separator in their locale settings, and that won't always be interpreted correctly.

As an example, you can use either of these functions in the WhereCondition of the OpenReport method (this code assumes that txtInvoiceDT is a text box on the form that the code is executing in):

DoCmd.OpenReport "MyReport", acViewPreview, , & _

         "InvoiceDate > " & SQLDate(Me!txtInvoiceDT)

The routine can also be used in generating a SQL string to be used elsewhere in your application (again, txtStartDT and txtEndDT are text boxes on your form):

   strSQL = "SELECT Field1, Field2 " & _

            "FROM MyTable " & _

            "WHERE ActiveDTM Between " & _

            SQLDate(Me!txtStartDT) & _

            " AND " & SQLDate(Me!txtEndDT)

One advantage of wrapping all your dates in such a function is that it becomes easy to reformat your SQL strings if you start using a different DBMS. For example, while Jet prefers dates to look like #mm/dd/yyyy#, SQL Server prefers 'yyyy-mm-dd', and the ODBC canonical format is {d 'yyyy-mm-dd'}. If you know that you're going to migrate from Jet to another DBMS, you can build this into your SQLDate function. By passing a second parameter to the function, you can have it automatically return the date in the appropriate format for use with the specific DBMS you're using:

Function SQLDate(DateIn As Variant, _

  Optional DataSource As String = "Jet") _

 As String

If IsDate(DateIn) Then

 Select Case UCase$(DataSource)

   Case "JET"

     SQLDate = Format$(DateIn, "\#yyyy\-mm\-dd\#")

   Case "SQL"

     SQLDate = Format$(DateIn, "\'yyyy\-mm\-dd\'")

   Case "ODBC"

     SQLDate = Format$(DateIn, _

                        "\{\d \'yyyy\-mm\-dd\'}")

 End Select

End If

End Function
 

How can I ensure that I get the right date from my users to put it into the database?

So now it's a case of retrieving the date that you'll pass to this routine. Consider the case where you're trying to create a date when you know the year, month, and day as individual data elements. Since you don't know what Short Date format your user will have, you typically want to avoid having Access coerce the date because, as you've seen, the results can be different. The CDate function (as well as the CVDate function) respects the user's locale settings. That means you shouldn't rely on those functions to convert your string to a date. Rather, you should use the DateSerial function.

On the other hand, if you're letting the users type a date into an unbound text box, using the CDate function (or CVDate function) in your code will ensure that you interpret the users' input as they intended (provided, of course, that the locale is set appropriately on their machine). While the Help file states that the CVDate function is only "provided for compatibility with previous versions of Visual Basic," it does have an advantage when dealing with Date fields in queries: CDate fails on Null values, whereas CVDate doesn't.

The last area that I need to discuss is recognizing user input as dates. As you may suspect, since you don't necessarily know what your user's locale settings have for Short Date format, once you've ensured that you're using the octothorpes and four-digit years this is where most of the problems occur. When you're using a text box bound to a Date field, it's not that difficult to manage date formats, as you'll get an error if Access can't interpret the input as a date. There's still the possibility of the user using the wrong date format when keying. One solution is to set the field's format to something unambiguous so that the users get unambiguous feedback on how their date is being interpreted. Figure 1 shows an example of how to set the field. Figure 2 shows the user entering the date in her preferred format. Finally, Figure 3 shows the field after the date has been entered.

AAFig1 Figure 1

AAFig2 Figure 2

AAFig3 Figure 3

Now the user has immediate confirmation that Access has correctly interpreted the date that she input. Note that this approach will work equally well with unbound forms. (In fact, setting the format of the field is required to ensure that Access will attempt to coerce the input into a date with unbound forms.)

Of course, a much safer approach is to use a calendar control for input (Incuded with Access 2007+) so that there's no chance of misinterpretation. However, in some instances, using a control can slow down data entry. Unfortunately, the use of calendars on forms is a bigger topic than I can get into in this column.

 

Your download file is called  309steele.ZIP in the file SA2003-09down.zip

This can be purchased with all the other downloads on this page

 

Tip:  In Access and Excel - if you press Control+D keys together, today's date will be inserted into a field