«

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

Aloof wrote:
> 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=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.

Aloof wrote:
> 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