I'm trying to return some records from an SQL Server 7 Database using date
restrictions but receive a message saying that there is an error near the #.
Several books say that you must use pound (#) signs when using Date
Restrictions.

I've enclosed the code that I am using and was wondering if someone can
point me in the right direction as to why this won't work. I've formatted
the date in American Format (I'm in Australia) but I still have no joy.

-------------Code Start
Set gds = gcnSg.OpenResultset("SELECT Despatched, DateReceived, InventoryID
FROM tblInventory WHERE Despatched = 0 AND tblInventory.DateReceived < #" &
Format$(Now, "mm/dd/yyyy") & "#", rdOpenStatic)
-------------Code End

The other problem I have is that when I do an SQL Execute Statement for
example ("UPDATE tblFax SET DateFaxed = " & Format$(Now, "Short Date")
the date inserts in mm/dd/yyy format, even if I try and format it myself. I
have told the ODBC driver to use Regional Settings as well. The only way I
can get the dates to work properly is by recursing through a recordset and
setting the date myself but this isn't very client/server friendly !

Any help would be greatly appreciated !

Regards,

David Webb
IT Manager
Super A-Mart Pty. Ltd.
Tel (07) 3343 9277
Fax (07) 3343 8566

Usage of (#)-signs is specific to the JET used by Access. SQL-Server doesn't
recognize this.

You may use "... WHERE Date = MM/DD/YYYY" to update a date-field. IMHO, I
prefer using the convert-function:

"... WHERE Date = Convert(datetime,'dd.mm.yyyy',104)"

See T-SQL-help for more info.

Olaf