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

[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.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 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_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 Harris]

"MacDermott" <ma********@nospam.com> wrote in message
news:Fk****************@newsread1.news.atl.earthli nk.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.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]

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.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

[DFS]

Phil,

Try

Format(event_date,"yyyy") = '2004'

or

Format(event_date,"yyyy") = 2004


"Phil Powell" <so*****@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 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

"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:o6*******************@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" <so*****@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