About using different calendars

Microsoft Office Access 2003

Show All Show All

About using different calendars

Some of the content in this topic may not be applicable to some languages.

Access supports use of the Hijri calendar in addition to the standard Gregorian calendar in all locales except Thai and East Asian locales.

If you open an existing database with date references and you have selected the wrong calendar setting, the program might report a variety of errors or other unexpected behaviors. For example, if you have checked the Hijri Calendar setting, Access will display a syntax error message if a report makes reference to Gregorian dates that are invalid as Hijri dates (for example, the date 12/31/99 will produce a syntax error because no Hijri month has 31 days).

To avoid problems of this sort, all date references in a database must be based on the same calendar.

Important  It is recommended that you choose the database calendar when you first create your database, and then never change it.

Other considerations when the database calendar is set to Hijri

  • To ensure Hijri dates strings are evaluated correctly in expressions and Visual Basic for Applications statements, use the CDate function to evaluate the strings instead of declaring the strings as constants.

    For example, type CDate("1/25/20") instead of #1/25/20#.

    The CDate function will always return a value based on the active database calendar, whereas date constants are always evaluated by using the Gregorian calendar.

  • For reliable behavior, enter and display dates in an unambiguous format. For example, dates in short date format might be misinterpreted in queries if the year or the day of the month are 12 or less (for example, 3/11/10). Dates in medium date format display only the first few characters of the month name, which can create ambiguity or an undesirable appearance. Use long date format whenever possible. You can alter the way a date is displayed in long format (for example, excluding the day of the month) by using the regional settings in Microsoft Windows Control Panel.
  • Dates entered in the ValidationRule property of a Date/Time field in a table (in table Design view) must be Hijri. However, to have the ValidationRule date appear as a Hijri date in validation messages, the validation rule must use the CDate function (for example, 'CDate ("1/25/1420")'.
  • Sample Access applications, such as the Northwind database, are not localized and expect the database calendar to be Gregorian. Because the sample applications contain hard-coded date constants that are not valid Hijri dates, running the sample applications with the database calendar set to Hijri is not recommended.
  • All Hijri years will be displayed as four-digit year values (for example, 1420) unless the Format() function is explicitly used.
  • The Visual Basic Date function, used to set the system date for your computer, will not accept Hijri dates. If the database calendar is set to Hijri, set the calendar to Gregorian before setting system date with the Date function.
  • Dates in tables exported as text files are always stored as Gregorian values. If the database calendar is set to Hijri, automatic Hijri to Gregorian conversion is performed during the export process. Similarly, dates in tables imported from text files are assumed to be Gregorian dates and will be converted to Hijri values during the import process if the database calendar setting is Hijri.