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 [url]www.brinkster.com[/url] 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:
[url]http://www3.brinkster.com/soa/val/event/calendar.asp[/url]



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" <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 [url]www.brinkster.com[/url] 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:
> [url]http://www3.brinkster.com/soa/val/event/calendar.asp[/url]
>
>
>
> Help!
>
>
>
> Thanx
>
> Phil
>
>
>
>
>
>

[Randy Harris]

"MacDermott" <macdermott@nospam.com> wrote in message
news:Fk0Kb.8343$6B.1786@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" <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 [url]www.brinkster.com[/url]

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:
> > [url]http://www3.brinkster.com/soa/val/event/calendar.asp[/url]
> >
> >
> >
> > 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.

[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" <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 [url]www.brinkster.com[/url] 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:
> [url]http://www3.brinkster.com/soa/val/event/calendar.asp[/url]
>
>
>
> 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" <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 [url]www.brinkster.com[/url]

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:
> > [url]http://www3.brinkster.com/soa/val/event/calendar.asp[/url]
> >
> >
> >
> > Help!
> >
> >
> >
> > Thanx
> >
> > Phil
> >
> >
> >
> >
> >
> >

>
>