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

[Phil Powell]

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]

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.

 

[Peter Doering]

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

 

[Wayne Morgan]

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.

 

[Phil Powell]

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