FP2002: Error Querying Date/Time Fields in Access Databases

When you attempt to search a Microsoft Access database using a date/time field, the following error message appears:

The code in the Database Results Wizard or the Database Interface Wizard that is generated by FrontPage, does not support querying a date/time data type in an Access database.

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. To work around this behavior and query the date/time field in an Access database, generate the database results region and then modify the code to pass a date/time value. You will need to change the apostrophes to number signs in the SQL SELECT statement. To do this, follow these steps.

NOTE: The following procedure demonstrates how to do this by using the Employees table in the Northwind database. You will need to substitute your database, table, and code for that shown in this procedure.

1. On the Insert menu, point to Database, and then click Results.
2. Click Use a sample database connection (Northwind), and then click Next.
3. Click Record source. In the Record source list, click Employees. Click Next.
4. Click More Options.
5. In the More Options dialog box, click Criteria.
6. In the Criteria dialog box, click Add.
7. In the Add Criteria dialog box, perform the following:
   1. In the Field Name list, click HireDate.
   2. In the Comparison list, click Equals.
   3. In the Value box, type HireDate if it doesn't appear by default.
   4. Click OK.
8. Click OK two more times.
9. Click Next two times.
10. Click Finish.
11. After the wizard has finished, click HTML at the bottom of the FrontPage window.
12. On the Edit menu, click Find. In the Find What box, type the following:

    s-sql="SELECT * FROM Employees WHERE (HireDate =  '::HireDate::')"
    					

13. Change each apostrophe (') to a number sign (#), so that it looks similar to this:

    s-sql="SELECT * FROM Employees WHERE (HireDate =  #::HireDate::#)"
    					

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.