error '80040e07' wrong syntax in Date expression

Discussion in 'ASP General' started by Aloof, Jun 29, 2005.

  1. Aloof

    Aloof Guest

    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, Jun 29, 2005
    #1
    1. Advertising

  2. 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=...=1&selm=ukS$6S$

    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
    --
    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.
     
    Bob Barrows [MVP], Jun 29, 2005
    #2
    1. Advertising

  3. Aloof

    Paxton Guest

    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
     
    Paxton, Jun 30, 2005
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Peter Grison

    Date, date date date....

    Peter Grison, May 28, 2004, in forum: Java
    Replies:
    10
    Views:
    3,370
    Michael Borgwardt
    May 30, 2004
  2. Steve Austin

    java.sql.Date returning wrong date

    Steve Austin, Mar 30, 2007, in forum: Java
    Replies:
    7
    Views:
    4,647
    Oliver Wong
    Apr 5, 2007
  3. Replies:
    1
    Views:
    1,500
    Ian Collins
    Jun 20, 2007
  4. David Woodward
    Replies:
    5
    Views:
    444
    Dr John Stockton
    Feb 2, 2004
  5. Mark Richards
    Replies:
    3
    Views:
    348
    Tad McClellan
    Nov 18, 2007
Loading...

Share This Page