Can't find syntax error in Access query - lost!

Discussion in 'ASP General' started by Phil Powell, Jan 4, 2004.

  1. Phil Powell

    Phil Powell Guest

    select event_date, event_name, event_text, event_is_public,
    event_is_reserved, event_img_path, event_img_alt, event_member_id,
    event_is_email_notify from event where show_entry = '1' and
    ((Year(event_date) = #2004# and event_is_reserved = '0') or
    event_is_reserved = '1' )

    produces this error:

    Microsoft JET Database Engine error '80040e07'

    Syntax error in date in query expression 'show_entry = '1' and
    ((Year(event_date) = #2004# and event_is_reserved = '0') or
    event_is_reserved = '1' )'.

    /soa/val/event/calendar.asp, line 258



    I'm totally lost here and I don't have the original .sql statement to have
    produced the "event" table schema, so that won't help either. Here is line
    258+:

    sql = "select event_date, event_name, event_text, event_is_public, " &_
    " event_is_reserved, event_img_path, event_img_alt,
    event_member_id, " &_
    " event_is_email_notify " &_
    "from event " &_
    "where show_entry = '1' " &_
    " and ((Year(event_date) = #" & DatePart("YYYY", Date) & "# and
    event_is_reserved = '0') or " &_
    " event_is_reserved = '1' " &_
    " )"
    Response.Write(sql)
    set rs = conn.execute(sql)



    Guys, I don't know what to do and I'm in a huge deadline to have this fixed
    by Monday AM and I'm completely stuck. Furthermore, I have no way of
    accessing the original Access .mb file since it's on www.brinkster.com and
    they forbid you from directly accessing any .mb file w/o their web-based
    tool (which cannot tell you what the schema is).



    My goal is to filter out a list of events from the event table where either
    the event_is_reserved field is '1' or if both the event_is_reserved field is
    '0' and the event_date is of the current year (event_date's format is
    '/mm/dd/yyyy').



    Original site breakage at:
    http://www3.brinkster.com/soa/val/event/calendar.asp



    Help!



    Thanx

    Phil
     
    Phil Powell, Jan 4, 2004
    #1
    1. Advertising

  2. Phil Powell

    Randy Harris Guest

    "MacDermott" <> wrote in message
    news:Fk0Kb.8343$...
    > I don't know if this is your problem, but the Year() function returns a
    > variant of type Integer, so you don't need the # delimiters around 2004.
    >
    > HTH
    > - Turtle


    Also, DatePart does not return a date, but rather a variant of type integer.


    > "Phil Powell" <> wrote in message
    > news:KX%Jb.69002$hf1.48297@lakeread06...
    > > select event_date, event_name, event_text, event_is_public,
    > > event_is_reserved, event_img_path, event_img_alt, event_member_id,
    > > event_is_email_notify from event where show_entry = '1' and
    > > ((Year(event_date) = #2004# and event_is_reserved = '0') or
    > > event_is_reserved = '1' )
    > >
    > > produces this error:
    > >
    > > Microsoft JET Database Engine error '80040e07'
    > >
    > > Syntax error in date in query expression 'show_entry = '1' and
    > > ((Year(event_date) = #2004# and event_is_reserved = '0') or
    > > event_is_reserved = '1' )'.
    > >
    > > /soa/val/event/calendar.asp, line 258
    > >
    > >
    > >
    > > I'm totally lost here and I don't have the original .sql statement to

    have
    > > produced the "event" table schema, so that won't help either. Here is

    > line
    > > 258+:
    > >
    > > sql = "select event_date, event_name, event_text, event_is_public, " &_
    > > " event_is_reserved, event_img_path, event_img_alt,
    > > event_member_id, " &_
    > > " event_is_email_notify " &_
    > > "from event " &_
    > > "where show_entry = '1' " &_
    > > " and ((Year(event_date) = #" & DatePart("YYYY", Date) & "# and
    > > event_is_reserved = '0') or " &_
    > > " event_is_reserved = '1' " &_
    > > " )"
    > > Response.Write(sql)
    > > set rs = conn.execute(sql)
    > >
    > >
    > >
    > > Guys, I don't know what to do and I'm in a huge deadline to have this

    > fixed
    > > by Monday AM and I'm completely stuck. Furthermore, I have no way of
    > > accessing the original Access .mb file since it's on www.brinkster.com

    and
    > > they forbid you from directly accessing any .mb file w/o their web-based
    > > tool (which cannot tell you what the schema is).
    > >
    > >
    > >
    > > My goal is to filter out a list of events from the event table where

    > either
    > > the event_is_reserved field is '1' or if both the event_is_reserved

    field
    > is
    > > '0' and the event_date is of the current year (event_date's format is
    > > '/mm/dd/yyyy').
    > >
    > >
    > >
    > > Original site breakage at:
    > > http://www3.brinkster.com/soa/val/event/calendar.asp
    > >
    > >
    > >
    > > Help!
    > >
    > >
    > >
    > > Thanx
    > >
    > > Phil
    > >
    > >
    > >
    > >
    > >
    > >

    >
    >
     
    Randy Harris, Jan 4, 2004
    #2
    1. Advertising

  3. On Sun, 4 Jan 2004 16:51:36 -0500, Phil Powell wrote:

    > select event_date, event_name, event_text, event_is_public,
    > event_is_reserved, event_img_path, event_img_alt, event_member_id,
    > event_is_email_notify from event where show_entry = '1' and
    > ((Year(event_date) = #2004# and event_is_reserved = '0') or
    > event_is_reserved = '1' )
    >
    > produces this error:
    >
    > Microsoft JET Database Engine error '80040e07'
    >
    > Syntax error in date in query expression 'show_entry = '1' and
    > ((Year(event_date) = #2004# and event_is_reserved = '0') or
    > event_is_reserved = '1' )'.


    Remove the # around 2004. Year returns an integer, not a date type.

    Peter

    --
    No mails please.
     
    Peter Doering, Jan 4, 2004
    #3
  4. Phil Powell

    Wayne Morgan Guest

    As mentioned by others, Year(event_date) is an integer not a date, you need
    to remove the #'s. Also, you have single quotes around the numbers 0 and 1.
    Are these in a field defined as text or in a number field. If the field is a
    text field, the quotes should be there, if the field is a number field the
    quotes shouldn't be there.

    --
    Wayne Morgan
    Microsoft Access MVP


    "Phil Powell" <> wrote in message
    news:KX%Jb.69002$hf1.48297@lakeread06...
    > select event_date, event_name, event_text, event_is_public,
    > event_is_reserved, event_img_path, event_img_alt, event_member_id,
    > event_is_email_notify from event where show_entry = '1' and
    > ((Year(event_date) = #2004# and event_is_reserved = '0') or
    > event_is_reserved = '1' )
    >
    > produces this error:
    >
    > Microsoft JET Database Engine error '80040e07'
    >
    > Syntax error in date in query expression 'show_entry = '1' and
    > ((Year(event_date) = #2004# and event_is_reserved = '0') or
    > event_is_reserved = '1' )'.
    >
    > /soa/val/event/calendar.asp, line 258
    >
    >
    >
    > I'm totally lost here and I don't have the original .sql statement to have
    > produced the "event" table schema, so that won't help either. Here is

    line
    > 258+:
    >
    > sql = "select event_date, event_name, event_text, event_is_public, " &_
    > " event_is_reserved, event_img_path, event_img_alt,
    > event_member_id, " &_
    > " event_is_email_notify " &_
    > "from event " &_
    > "where show_entry = '1' " &_
    > " and ((Year(event_date) = #" & DatePart("YYYY", Date) & "# and
    > event_is_reserved = '0') or " &_
    > " event_is_reserved = '1' " &_
    > " )"
    > Response.Write(sql)
    > set rs = conn.execute(sql)
    >
    >
    >
    > Guys, I don't know what to do and I'm in a huge deadline to have this

    fixed
    > by Monday AM and I'm completely stuck. Furthermore, I have no way of
    > accessing the original Access .mb file since it's on www.brinkster.com and
    > they forbid you from directly accessing any .mb file w/o their web-based
    > tool (which cannot tell you what the schema is).
    >
    >
    >
    > My goal is to filter out a list of events from the event table where

    either
    > the event_is_reserved field is '1' or if both the event_is_reserved field

    is
    > '0' and the event_date is of the current year (event_date's format is
    > '/mm/dd/yyyy').
    >
    >
    >
    > Original site breakage at:
    > http://www3.brinkster.com/soa/val/event/calendar.asp
    >
    >
    >
    > Help!
    >
    >
    >
    > Thanx
    >
    > Phil
    >
    >
    >
    >
    >
    >
     
    Wayne Morgan, Jan 4, 2004
    #4
  5. Phil Powell

    Phil Powell Guest

    Actually the second part is fine, the event_is_reserved is of type char
    while event_date is a date field. I did not know that Year() converts a
    date type to an integer, thanx, everything works now.

    Phil

    "Wayne Morgan" <> wrote in message
    news:eek:61Kb.19381$...
    > As mentioned by others, Year(event_date) is an integer not a date, you

    need
    > to remove the #'s. Also, you have single quotes around the numbers 0 and

    1.
    > Are these in a field defined as text or in a number field. If the field is

    a
    > text field, the quotes should be there, if the field is a number field the
    > quotes shouldn't be there.
    >
    > --
    > Wayne Morgan
    > Microsoft Access MVP
    >
    >
    > "Phil Powell" <> wrote in message
    > news:KX%Jb.69002$hf1.48297@lakeread06...
    > > select event_date, event_name, event_text, event_is_public,
    > > event_is_reserved, event_img_path, event_img_alt, event_member_id,
    > > event_is_email_notify from event where show_entry = '1' and
    > > ((Year(event_date) = #2004# and event_is_reserved = '0') or
    > > event_is_reserved = '1' )
    > >
    > > produces this error:
    > >
    > > Microsoft JET Database Engine error '80040e07'
    > >
    > > Syntax error in date in query expression 'show_entry = '1' and
    > > ((Year(event_date) = #2004# and event_is_reserved = '0') or
    > > event_is_reserved = '1' )'.
    > >
    > > /soa/val/event/calendar.asp, line 258
    > >
    > >
    > >
    > > I'm totally lost here and I don't have the original .sql statement to

    have
    > > produced the "event" table schema, so that won't help either. Here is

    > line
    > > 258+:
    > >
    > > sql = "select event_date, event_name, event_text, event_is_public, " &_
    > > " event_is_reserved, event_img_path, event_img_alt,
    > > event_member_id, " &_
    > > " event_is_email_notify " &_
    > > "from event " &_
    > > "where show_entry = '1' " &_
    > > " and ((Year(event_date) = #" & DatePart("YYYY", Date) & "# and
    > > event_is_reserved = '0') or " &_
    > > " event_is_reserved = '1' " &_
    > > " )"
    > > Response.Write(sql)
    > > set rs = conn.execute(sql)
    > >
    > >
    > >
    > > Guys, I don't know what to do and I'm in a huge deadline to have this

    > fixed
    > > by Monday AM and I'm completely stuck. Furthermore, I have no way of
    > > accessing the original Access .mb file since it's on www.brinkster.com

    and
    > > they forbid you from directly accessing any .mb file w/o their web-based
    > > tool (which cannot tell you what the schema is).
    > >
    > >
    > >
    > > My goal is to filter out a list of events from the event table where

    > either
    > > the event_is_reserved field is '1' or if both the event_is_reserved

    field
    > is
    > > '0' and the event_date is of the current year (event_date's format is
    > > '/mm/dd/yyyy').
    > >
    > >
    > >
    > > Original site breakage at:
    > > http://www3.brinkster.com/soa/val/event/calendar.asp
    > >
    > >
    > >
    > > Help!
    > >
    > >
    > >
    > > Thanx
    > >
    > > Phil
    > >
    > >
    > >
    > >
    > >
    > >

    >
    >
     
    Phil Powell, Jan 5, 2004
    #5
    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. ad
    Replies:
    2
    Views:
    891
  2. Replies:
    4
    Views:
    249
    Bob Barrows
    Sep 30, 2005
  3. Darryl
    Replies:
    0
    Views:
    202
    Darryl
    Sep 28, 2005
  4. Mark Richards
    Replies:
    3
    Views:
    349
    Tad McClellan
    Nov 18, 2007
  5. Jason C
    Replies:
    14
    Views:
    564
    Kaz Kylheku
    Sep 26, 2012
Loading...

Share This Page