ACC2000: Using Hijri Dates for Criteria in .adp Files Results in Error Message (232064)

NOTE: This article discusses complex scripts functionality. Complex scripts functionality is available when you use the Office Language Settings tool to enable a language, such as Arabic or Hebrew, that requires this functionality. For more information about multilingual features, click Microsoft <I BRACKET="YES">your Office product</I> Help on the Help menu, type multilingual features in the Office Assistant or the Answer Wizard, and then click Search to view the topics that are returned.

Moderate: Requires basic macro, coding, and interoperability skills.

SYMPTOMS

If you try to run a criteria that contains Hijri dates in client/server databases, you may receive error messages, and then the operation fails.

CAUSE

Date criteria in client/server databases is handled as Gregorian. A Hijri date entered in a criteria expression is interpreted as Gregorian, and this results in an "out-of-range" message because Hijri dates fall below the minimum Gregorian date allowed by SQL server. Using the alternative method, CDATE("hijri date"), doesn't work because SQL server does not recognize this function.

RESOLUTION

Enter only Gregorian dates in criteria. If the database calendar is set to Hijri, use the Gregorian equivalent of the target date in the expression.

MORE INFORMATION

Steps to Reproduce Behavior

Start Access and create a new Microsoft Access project.
Create a table with an INT field (Primary) and a Date field
On the Tools menu, click Startup.
In the Startup dialog box, click Advanced, and set the database calendar to Hijri.
Open the table in Datasheet view and enter a few records.
Save and close the table.
Create a new View based on the table that you just created and include both fields.
For the date field criteria, type >[A date value from the table].
Save and run the View.

Note that you receive an "out-of-range" error message for the date value. If you were to use CDATE("any hijri date"), this operation would still would not be successful.