In Access the following SQL statement is valid for selecting a recordset from a date/time field

    SELECT [TableName].[Record Date]
    FROM [TableName]
    WHERE ((([TableName].[Record Date])=#10/1/2001 20:57:56#))

This query will not execute on MS SQL Server, it has to have a single quote ' instead of # as below

    SELECT [TableName].[Record Date]
    FROM [TableName]
    WHERE ((([TableName].[Record Date])='10/1/2001 20:57:56'))

It is a small thing but if you are moving legacy code from Access to SQL Server it can catch you out. I think it is Access that is using the nonstandard SQL syntax? [True] Access won't use the SQL Syntax suitable for SQL server so a program that executes on both databases has to know which type it is trying to connect to and change the syntax accordingly.

Note that you can also use MSDE instead of Access or SQL Server. MSDE is a free, slightly restricted version of SQL Server designed for smaller applications. You can build your initial application using MSDE and then easily upgrade to SQL Server if necessary. See Tip: Database Programming With MSDE for more information.

David Albrecht points out:

These appear to work only where the regional settings are set to US. Ie. #10/1/2001# would be treated as 1st October 2001 regardless of regional settings.

The way I get around this problem is to use the CDate function which is inherently built in to Access and uses your regional setting. E.g.

    SELECT [TableName].[Record Date]
    FROM [TableName]
    WHERE ((([TableName].[Record Date]) =
        CDate('1/10/2001 20:57:56')))
    ' 1st Oct in Australia, 10th Jan in US.