I have a serious problem with a sql-query using the CDate command on a Windows-System (german win 2000 server) using german Date Format. The Date locally cannot be changed to english short format, because of other programs.
The Dates in the Database are in US-Format: e.g. 01/30/2004
The Users enters a Form where he enters his date selection: e.g. from 02/10/2004 untill 04/30/2004. This dates are used to query the database with the following query:
SQL = "SELECT * FROM tblKundenstamm, tblVertrag " _
& "WHERE tblVertrag.strKSID = tblKundenstamm.IDRef AND tblKundenstamm.strFirma = '" & Request.Form("Kunde")
& "' AND CDate(strDate) BETWEEN #" & firstDate & "# AND #" & lastDate & "# " _
If the Date ist 12/20/2004 CDate converts it to 12.20.2004 which is correct. But if the Date looks like 02/10/2004 it is converted to 02.10.2004 which is not correct.
How can i correct this problem?
Thanks for your help