By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,078 Members | 2,164 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,078 IT Pros & Developers. It's quick & easy.

error '80040e07' wrong syntax in Date expression

P: n/a
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
Jul 22 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.
Jul 22 '05 #2

P: n/a
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

Jul 22 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.