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

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

MacDermott
Sun Jan 04 16:20:21 CST 2004

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

"Phil Powell" <soazine@erols.com> 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
>
>
>
>
>
>

Peter
Sun Jan 04 16:32:27 CST 2004

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.

Randy
Sun Jan 04 16:31:25 CST 2004

"MacDermott" <macdermott@nospam.com> wrote in message
news:Fk0Kb.8343$6B.1786@newsread1.news.atl.earthlink.net...
> 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" <soazine@erols.com> 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
Sun Jan 04 17:13:24 CST 2004

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" <soazine@erols.com> 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
Sun Jan 04 20:44:15 CST 2004

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" <comprev_gothroughthenewsgroup@hotmail.com> wrote in message
news:o61Kb.19381$1X5.14729@newssvr33.news.prodigy.com...
> 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" <soazine@erols.com> 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
> >
> >
> >
> >
> >
> >
>
>