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

[Phil Powell]

select event_date, event_name, event_text, event_is_public ,
event_is_reserv ed, event_img_path, event_img_alt, event_member_id ,
event_is_email_ notify from event where show_entry = '1' and
((Year(event_da te) = #2004# and event_is_reserv ed = '0') or
event_is_reserv ed = '1' )

produces this error:

Microsoft JET Database Engine error '80040e07'

Syntax error in date in query expression 'show_entry = '1' and
((Year(event_da te) = #2004# and event_is_reserv ed = '0') or
event_is_reserv ed = '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_reserv ed, event_img_path, event_img_alt,
event_member_id , " &_
" event_is_email_ notify " &_
"from event " &_
"where show_entry = '1' " &_
" and ((Year(event_da te) = #" & DatePart("YYYY" , Date) & "# and
event_is_reserv ed = '0') or " &_
" event_is_reserv ed = '1' " &_
" )"
Response.Write( sql)
set rs = conn.execute(sq l)

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_reserv ed field is '1' or if both the event_is_reserv ed 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]

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" <so*****@erols. com> wrote in message
news:KX%Jb.6900 2$hf1.48297@lak eread06...

select event_date, event_name, event_text, event_is_public ,
event_is_reserv ed, event_img_path, event_img_alt, event_member_id ,
event_is_email_ notify from event where show_entry = '1' and
((Year(event_da te) = #2004# and event_is_reserv ed = '0') or
event_is_reserv ed = '1' )

produces this error:

Microsoft JET Database Engine error '80040e07'

Syntax error in date in query expression 'show_entry = '1' and
((Year(event_da te) = #2004# and event_is_reserv ed = '0') or
event_is_reserv ed = '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_reserv ed, event_img_path, event_img_alt,
event_member_id , " &_
" event_is_email_ notify " &_
"from event " &_
"where show_entry = '1' " &_
" and ((Year(event_da te) = #" & DatePart("YYYY" , Date) & "# and
event_is_reserv ed = '0') or " &_
" event_is_reserv ed = '1' " &_
" )"
Response.Write( sql)
set rs = conn.execute(sq l)

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_reserv ed field is '1' or if both the event_is_reserv ed 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 Doering]

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

select event_date, event_name, event_text, event_is_public ,
event_is_reserv ed, event_img_path, event_img_alt, event_member_id ,
event_is_email_ notify from event where show_entry = '1' and
((Year(event_da te) = #2004# and event_is_reserv ed = '0') or
event_is_reserv ed = '1' )

produces this error:

Microsoft JET Database Engine error '80040e07'

Syntax error in date in query expression 'show_entry = '1' and
((Year(event_da te) = #2004# and event_is_reserv ed = '0') or
event_is_reserv ed = '1' )'.

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

Peter

--
No mails please.

[Randy Harris]

"MacDermott " <ma********@nos pam.com> wrote in message
news:Fk******** ********@newsre ad1.news.atl.ea rthlink.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" <so*****@erols. com> wrote in message
news:KX%Jb.6900 2$hf1.48297@lak eread06...

select event_date, event_name, event_text, event_is_public ,
event_is_reserv ed, event_img_path, event_img_alt, event_member_id ,
event_is_email_ notify from event where show_entry = '1' and
((Year(event_da te) = #2004# and event_is_reserv ed = '0') or
event_is_reserv ed = '1' )

produces this error:

Microsoft JET Database Engine error '80040e07'

Syntax error in date in query expression 'show_entry = '1' and
((Year(event_da te) = #2004# and event_is_reserv ed = '0') or
event_is_reserv ed = '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_reserv ed, event_img_path, event_img_alt,
event_member_id , " &_
" event_is_email_ notify " &_
"from event " &_
"where show_entry = '1' " &_
" and ((Year(event_da te) = #" & DatePart("YYYY" , Date) & "# and
event_is_reserv ed = '0') or " &_
" event_is_reserv ed = '1' " &_
" )"
Response.Write( sql)
set rs = conn.execute(sq l)

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_reserv ed field is '1' or if both the event_is_reserv ed

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]

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" <so*****@erols. com> wrote in message
news:KX%Jb.6900 2$hf1.48297@lak eread06...

select event_date, event_name, event_text, event_is_public ,
event_is_reserv ed, event_img_path, event_img_alt, event_member_id ,
event_is_email_ notify from event where show_entry = '1' and
((Year(event_da te) = #2004# and event_is_reserv ed = '0') or
event_is_reserv ed = '1' )

produces this error:

Microsoft JET Database Engine error '80040e07'

Syntax error in date in query expression 'show_entry = '1' and
((Year(event_da te) = #2004# and event_is_reserv ed = '0') or
event_is_reserv ed = '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_reserv ed, event_img_path, event_img_alt,
event_member_id , " &_
" event_is_email_ notify " &_
"from event " &_
"where show_entry = '1' " &_
" and ((Year(event_da te) = #" & DatePart("YYYY" , Date) & "# and
event_is_reserv ed = '0') or " &_
" event_is_reserv ed = '1' " &_
" )"
Response.Write( sql)
set rs = conn.execute(sq l)

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_reserv ed field is '1' or if both the event_is_reserv ed 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]

Actually the second part is fine, the event_is_reserv ed 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" <co************ *************** @hotmail.com> wrote in message
news:o6******** ***********@new ssvr33.news.pro digy.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" <so*****@erols. com> wrote in message
news:KX%Jb.6900 2$hf1.48297@lak eread06...

select event_date, event_name, event_text, event_is_public ,
event_is_reserv ed, event_img_path, event_img_alt, event_member_id ,
event_is_email_ notify from event where show_entry = '1' and
((Year(event_da te) = #2004# and event_is_reserv ed = '0') or
event_is_reserv ed = '1' )

produces this error:

Microsoft JET Database Engine error '80040e07'

Syntax error in date in query expression 'show_entry = '1' and
((Year(event_da te) = #2004# and event_is_reserv ed = '0') or
event_is_reserv ed = '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_reserv ed, event_img_path, event_img_alt,
event_member_id , " &_
" event_is_email_ notify " &_
"from event " &_
"where show_entry = '1' " &_
" and ((Year(event_da te) = #" & DatePart("YYYY" , Date) & "# and
event_is_reserv ed = '0') or " &_
" event_is_reserv ed = '1' " &_
" )"
Response.Write( sql)
set rs = conn.execute(sq l)

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_reserv ed field is '1' or if both the event_is_reserv ed

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