ACC2002: Incorrect Records Are Selected When the Criterion Is a Date and the Short Date Format Is d/M/yy (295698)

CAUSE

This behavior can occur because, when you select a Date/Time field as the criterion for record selection, the Command Button Wizard creates an event procedure that includes code similar to the following:

stLinkCriteria = "[Record Date]=" & "#" & Me![Combo0] & "#"
DoCmd.OpenForm stDocName, , , stLinkCriteria

When this code is run, the date value from the combo box or other control is interpreted as a short date in the format M/d/yy, regardless of the format chosen for the fields in question or in the Regional Options dialog box. As a result, a date such as July 10, 2000, may be represented as "10/7/00" in a table's data and in a combo box, but is interpreted as "7/10/00," or October 7, 2000, when the command button is clicked. This may cause the wrong record, or an empty record, to be displayed in the form that is opened by the command button.

RESOLUTION

You can work around this issue through either of two methods, depending on whether the data-matching process is intended to select one specific record or a collection of records based on the selected date.

Method 1: Include the Format() Function in the Selection Code

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. You can work around this issue by modifying the code produced by the Command Button Wizard so that the date is specifically presented in Medium Date format, which is interpreted correctly for all dates.

For an example of how to do this, follow these steps after performing the steps in the "Steps to Reproduce the Problem" section of this article:

Open the form Test Form in Design view.
Right-click the command button, and then click Build Event on the shortcut menu.

Revise the following line

stLinkCriteria = "[Record Date] = " & "#" & Me![Combo0] & "#"

to the following:

stLinkCriteria = "[Record Date] = " & "#" & Format(Me![Combo0],"Medium Date") & "#"

For more information about the Format() function, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type named date/time formats (format function) in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Method 2: Use a Separate Key Field to Select One Specific Record

If the date field uniquely identifies a particular record, as in the preceding sample code, you can work around this behavior by adding an AutoNumber field to the table as its primary key and using the key value in the record selection. Then, re-create the controls that provide the lookup information and open the second form.

MORE INFORMATION

The Command Button Wizard enables you to create a command button on one form that opens a second form that displays only those records that match specific data identified on the first form. If the matching data are of the Date/Time type, the computer's Short date format setting influences the matching process and can lead to the selection of incorrect records.

The Short date format setting of M/d/yy is common in the United States, and represents the date October 7, 2000 as "10/7/00." In other parts of the world, including the United Kingdom and Commonwealth countries, the same date is often represented as "7/10/00" -- that is, in a Short date format setting of d/M/yy. While d/M/yy is not a listed option under Short date format, it can be set by a user with Administrator privileges. If that has been done, Access may not return the correct records when forms are opened, as illustrated in the "Steps to Reproduce the Problem" section.

When the Short date format setting is M/d/yy, all the records are displayed as expected. However, when the Short date format setting is d/M/yy, only the records for dates after the 12th of the month are selected correctly. If you select a date that is earlier than the 13th (for example, October 7, 2000, in the following sample data) you may find that the form opens to the record for an incorrect date, such as July 10, 2000, in this example, or to a new record (as though the required data does not exist) if there is no record for July 10, 2000.

Steps to Reproduce the Problem

NOTE: Because there are several versions of Microsoft Windows, the following steps may be different on your computer. If they are, see your product documentation to complete these steps.

Click Start, point to Settings, and then click Control Panel. Double-click Regional Options (or Regional Settings in Microsoft Windows 98), and then click the Date tab. Note the setting for Short date format, and then select M/d/yy.
Start Access and create a new database named DateTest.mdb.

Create the following table named Date List:

   Table: Date List
   ----------------------------
   Field Name: Record Date
   Data Type: Date/Time
   Indexed: Yes (No Duplicates)

   Field Name: Record Note
   Data Type: Text

   Table Properties: Date List
   -------------------------------
   PrimaryKey: Record Date

Use AutoForm to create a form based on the Date List table, accepting the default name "Date List" for the form.

Using either the form or datasheet view of the table, enter the following data, making sure that you enter the same keystrokes in the Record Note field that you used in the Record Date field to preserve a record of your data entry.

    Record Date        Record Note
    -----------        -----------
    10 Jul 00           10 Jul 00
     7 Oct 00            7 Oct 00
    30 Oct 00           30 Oct 00
    11 Nov 00           11 Nov 00
    13 Nov 00           13 Nov 00

Create a new form, not based on any table, in Design view.
Ensure that the Control Wizard on the Toolbox is turned on, and then place an unbound combo box on the form. In the Combo Box Wizard:
1. Select look up the values, and then click Next.
2. Select the table Date List, and then click Next.
3. Select all fields, and then click Next.
4. Click to clear the Hide key column check box, and then click Next.
5. Select Record Date as the bound column, and then click Finish.
Place a command button on the form, and then follow these steps in the Command Button Wizard:
1. Click Form Operations under Categories, click Open Form under Actions, and then click Next.
2. Select the form Date List, and then click Next.
3. Select Open the form and find specific data, and then click Next.
4. Click Combo0 under Form1, click Record Date under Date List, and then click the match button (<->). Click Finish.
Save the form as Test Form, and then open it in Form view.
Use the combo box to select the first record, 10 Jul 00, and then click the command button to open the Date List form.

Notice whether or not the correct record is displayed. Repeat this for other dates listed in the combo box.
Again open the Regional Options dialog box, and then select d/M/yy as the Short date format setting.

You may need Administrator privileges to make this change.
Repeat step 10. Notice that when you select the first record (Record Note = "10 Jul 00"), the record displayed is the second record (Record Note = "7 Oct 00").