ACC2000: Date Expression for Filter Criteria in a Bound Form Does Not Always Filter Hijri or Gregorian Dates as Expected (231929)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q231929
Moderate: Requires basic macro, coding, and interoperability skills.

SYMPTOMS

Under certain circumstances, the following behaviors may occur:
  • Hijri dates entered in Filter criteria are interpreted as Gregorian dates.
  • Short format Gregorian dates entered in Filter criteria are assumed to have mm/dd/yy format, regardless of Control Panel settings.
You may receive incorrect results if you enter Filter criteria without regard to these behaviors.

CAUSE

When you use the format #date# to specify a date value in a query, the date is always interpreted as a Gregorian value. In the example described in the "More Information" section later in this article, the date 2/4/18 is interpreted as 2/4/2018 instead of 2/4/1418 (Gregorian year 1997); this returns the wrong results when the filter example (in the "More Information" section) is run. Also, note that if the database calendar is set to Gregorian, you still get the wrong results if the date is entered in dd/mm/yy format.

RESOLUTION

Use the following workarounds:
  1. When you use the #date# format to specify a date criteria, enter the date value as a Gregorian value.
  2. When you use the #date# format to specify a date criteria, enter a Gregorian short date value in the mm/dd/yy format, regardless of the format in Regional Settings tool. To avoid ambiguity in date interpretation, enter dates in long format instead of short format.
  3. For maximum flexibility, use the CDATE("date") format to specify the date value in the Filter criteria. Both Gregorian and Hijri dates are recognized according to the Use Hijri Calendar check box setting (see the "More Information" section), and the short date format from the Control Panel setting is used. In this case, workarounds 1 and 2 are not be necessary.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Click Start, point to Settings, and then click Control Panel.
  2. In Control Panel, double-click Regional Settings.
  3. In the Regional Settings Properties dialog box, click the Date tab, and click dd/mm/yy in the Short date style list.
  4. Start Access and open any database.
  5. On the Tools menu, click Startup.
  6. In the Startup dialog box, click Advanced, and click to select the Use Hijri Calendar check box to set the database calendar to Hijri.
  7. Create a new table with a Date/Time field and name the field "mydate".
  8. Enter a few date values in the Date field (for example enter values from 2/1/18 to 2/8/18).
  9. Create a form based on the new table and add the "mydate" field to the form.
  10. Set the Filter property of the form to [mydate]>#2/4/18#.
  11. View the form in Form view and click the Apply Filter button on the toolbar.

    Note that no results are returned. You would expect to see all records from 2/5/18 to 2/8/18.

    To get the correct results, set the Filter property to [mydate]>CDATE("2/4/18").
Modification Type:MajorLast Reviewed:6/28/2004
Keywords:kbBidi kbprb KB231929
©2004 Microsoft Corporation. All rights reserved.