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("S tartDateMonth") & "/" & Request.Form
("StartDateDay" ) & "/" & Request.Form("S tartDateYear")
EndDate = Request.Form("E ndDateMonth") & "/" & Request.Form
("EndDateDay ") & "/" & Request.Form("E ndDateYear")

sql = "SELECT DISTINCT tblSessions.Vis itorID FROM tblSessions WHERE
tblSessions.Dat eTimeEntrance Between #" & StartDate & " 0:0:1# And #" &
EndDate & " 23:59:59#;"

set RSVisitors = cn.execute("sql ")

Response.write sql gives:

SELECT DISTINCT tblSessions.Vis itorID FROM tblSessions WHERE
tblSessions.Dat eTimeEntrance 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.Da teTimeEntrance
Between #06/01/2005 0:0:1# And #06/31/2005 23:59:59#'.

/stats/stats_detail.as p, 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]]

Aloof wrote:

Using Access 2000
Windows Server 2003

The following code worked fine until we moved hosting companies

StartDate = Request.Form("S tartDateMonth") & "/" & Request.Form
("StartDateDay" ) & "/" & Request.Form("S tartDateYear")
EndDate = Request.Form("E ndDateMonth") & "/" & Request.Form
("EndDateDay ") & "/" & Request.Form("E ndDateYear")

sql = "SELECT DISTINCT tblSessions.Vis itorID FROM tblSessions WHERE
tblSessions.Dat eTimeEntrance Between #" & StartDate & " 0:0:1# And #"
& EndDate & " 23:59:59#;"

set RSVisitors = cn.execute("sql ")

Response.write sql gives:

SELECT DISTINCT tblSessions.Vis itorID FROM tblSessions WHERE
tblSessions.Dat eTimeEntrance 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.Da teTimeEntrance
Between #06/01/2005 0:0:1# And #06/31/2005 23:59:59#'.

/stats/stats_detail.as p, 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=e...TNGP11.phx.gbl

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/...e36562fee7804e

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Paxton]

Aloof wrote:

Using Access 2000
Windows Server 2003

The following code worked fine until we moved hosting companies

StartDate = Request.Form("S tartDateMonth") & "/" & Request.Form
("StartDateDay" ) & "/" & Request.Form("S tartDateYear")
EndDate = Request.Form("E ndDateMonth") & "/" & Request.Form
("EndDateDay ") & "/" & Request.Form("E ndDateYear")

sql = "SELECT DISTINCT tblSessions.Vis itorID FROM tblSessions WHERE
tblSessions.Dat eTimeEntrance Between #" & StartDate & " 0:0:1# And #" &
EndDate & " 23:59:59#;"

set RSVisitors = cn.execute("sql ")

Response.write sql gives:

SELECT DISTINCT tblSessions.Vis itorID FROM tblSessions WHERE
tblSessions.Dat eTimeEntrance 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.Da teTimeEntrance
Between #06/01/2005 0:0:1# And #06/31/2005 23:59:59#'.

/stats/stats_detail.as p, 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