error '80040e07' wrong syntax in Date expression

[Aloof]

Using Access 2000
Windows Server 2003

The following code worked fine until we moved hosting companies

StartDate = Request.Form("StartDateMonth") & "/" & Request.Form
("StartDateDay") & "/" & Request.Form("StartDateYear")
EndDate = Request.Form("EndDateMonth") & "/" & Request.Form
("EndDateDay") & "/" & Request.Form("EndDateYear")

sql = "SELECT DISTINCT tblSessions.VisitorID FROM tblSessions WHERE
tblSessions.DateTimeEntrance Between #" & StartDate & " 0:0:1# And #" &
EndDate & " 23:59:59#;"

set RSVisitors = cn.execute("sql")

Response.write sql gives:

SELECT DISTINCT tblSessions.VisitorID FROM tblSessions WHERE
tblSessions.DateTimeEntrance Between #06/01/2005 0:0:1# And #06/31/2005
23:59:59#;

Now the same code gives an error message:


Microsoft JET Database Engine error '80040e07'

Syntax error in date in query expression 'tblSessions.DateTimeEntrance
Between #06/01/2005 0:0:1# And #06/31/2005 23:59:59#'.

/stats/stats_detail.asp, line 33

The old hosting company was Win2003, as is the new company. I've tried
changing the order of the date parts (YYYY/MM/DD, MM/DD/YYYY,
YYYY/DD/MM) but nonoe of the formats make a difference.

Any ideas?

TIA

 

[Bob Barrows [MVP]]

Using Access 2000
Windows Server 2003

The following code worked fine until we moved hosting companies

StartDate = Request.Form("StartDateMonth") & "/" & Request.Form
("StartDateDay") & "/" & Request.Form("StartDateYear")
EndDate = Request.Form("EndDateMonth") & "/" & Request.Form
("EndDateDay") & "/" & Request.Form("EndDateYear")

sql = "SELECT DISTINCT tblSessions.VisitorID FROM tblSessions WHERE
tblSessions.DateTimeEntrance Between #" & StartDate & " 0:0:1# And #"
& EndDate & " 23:59:59#;"

set RSVisitors = cn.execute("sql")

Response.write sql gives:

SELECT DISTINCT tblSessions.VisitorID FROM tblSessions WHERE
tblSessions.DateTimeEntrance Between #06/01/2005 0:0:1# And
#06/31/2005 23:59:59#;

Now the same code gives an error message:


Microsoft JET Database Engine error '80040e07'

Syntax error in date in query expression 'tblSessions.DateTimeEntrance
Between #06/01/2005 0:0:1# And #06/31/2005 23:59:59#'.

/stats/stats_detail.asp, line 33

The old hosting company was Win2003, as is the new company. I've
tried changing the order of the date parts (YYYY/MM/DD, MM/DD/YYYY,
YYYY/DD/MM) but nonoe of the formats make a difference.

Any ideas?

The safest format to use is #YYYY-MM-DD hh:mm:ss# (note the hyphens), so if
you persist in using dynamic sql, that is the format you should use.

You would be better off using parameters, either via saved parameter
queries:
http://groups.google.com/groups?hl=...=1&[email protected]

or using ODBC parameter markers in your sql string, and using a Command
object to pass the parameter values:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

Bob Barrows

 

[Paxton]

Using Access 2000
Windows Server 2003

The following code worked fine until we moved hosting companies

StartDate = Request.Form("StartDateMonth") & "/" & Request.Form
("StartDateDay") & "/" & Request.Form("StartDateYear")
EndDate = Request.Form("EndDateMonth") & "/" & Request.Form
("EndDateDay") & "/" & Request.Form("EndDateYear")

sql = "SELECT DISTINCT tblSessions.VisitorID FROM tblSessions WHERE
tblSessions.DateTimeEntrance Between #" & StartDate & " 0:0:1# And #" &
EndDate & " 23:59:59#;"

set RSVisitors = cn.execute("sql")

Response.write sql gives:

SELECT DISTINCT tblSessions.VisitorID FROM tblSessions WHERE
tblSessions.DateTimeEntrance Between #06/01/2005 0:0:1# And #06/31/2005
23:59:59#;

Now the same code gives an error message:


Microsoft JET Database Engine error '80040e07'

Syntax error in date in query expression 'tblSessions.DateTimeEntrance
Between #06/01/2005 0:0:1# And #06/31/2005 23:59:59#'.

/stats/stats_detail.asp, line 33

The old hosting company was Win2003, as is the new company. I've tried
changing the order of the date parts (YYYY/MM/DD, MM/DD/YYYY,
YYYY/DD/MM) but nonoe of the formats make a difference.

Any ideas?

TIA

In addition to Bob's reply, there is no 31st June. That's why your
expression doesn't evaluate. You need to test for/prevent invalid
dates being entered into your form.

Paxtonend