Thomas & Jon and all others who are more educated than I at this

I have succeeded at pulling the monthly information from an Access DB for my
home page. The next step is that I need to pull the next occurence and only
the next occurence for a particular event and place that in a results box.
Here is what I have so far and FP cannot verify this query:

SELECT TOP 1 MonthName([EventMonth],True)+" "+Str([EventDay]) AS Expr1
FROM Main
WHERE (((Main.EventDate)>=Date()) AND ((Main.EventDetail)="drill"))
ORDER BY Main.EventDate;

Hi Mike,

monthname wants the monthnumber (eg 1) not the whole date -

SELECT TOP 1 MonthName(Month(EventMonth),1)+' '+EventDay AS Expr1
FROM [Main]
WHERE EventDate >=Date() AND EventDetail='drill'
ORDER BY EventDate

I've also replaced double quotes with single - Access doesn't mind either
but it's not a good habit - if you move to another db (eg SQL Server) you'll
find you can't use double quotes. Think main is a reserved name too so I've
stuck it in [ ] to be on the safe side

Jon
Microsoft MVP - FP

"Mike Mueller" <(E-Mail Removed)> wrote in message
news:#DTj#(E-Mail Removed)...
> Thomas & Jon and all others who are more educated than I at this
>
> I have succeeded at pulling the monthly information from an Access DB for
my
> home page. The next step is that I need to pull the next occurence and
only
> the next occurence for a particular event and place that in a results box.
> Here is what I have so far and FP cannot verify this query:
>
> SELECT TOP 1 MonthName([EventMonth],True)+" "+Str([EventDay]) AS Expr1
> FROM Main
> WHERE (((Main.EventDate)>=Date()) AND ((Main.EventDetail)="drill"))
> ORDER BY Main.EventDate;
>
>

Hi Jon,

http://www.xmas-i-am.com/reserved_words.htm

I don't think Main is on the list...
But better to be safe than sorry....

xmas
=======================
"Jon Spivey" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
| Hi Mike,
|
| monthname wants the monthnumber (eg 1) not the whole date -
|
| SELECT TOP 1 MonthName(Month(EventMonth),1)+' '+EventDay AS Expr1
| FROM [Main]
| WHERE EventDate >=Date() AND EventDetail='drill'
| ORDER BY EventDate
|
| I've also replaced double quotes with single - Access doesn't mind either
| but it's not a good habit - if you move to another db (eg SQL Server)
you'll
| find you can't use double quotes. Think main is a reserved name too so
I've
| stuck it in [ ] to be on the safe side
|
| Jon
| Microsoft MVP - FP
|
| "Mike Mueller" <(E-Mail Removed)> wrote in message
| news:#DTj#(E-Mail Removed)...
| > Thomas & Jon and all others who are more educated than I at this
| >
| > I have succeeded at pulling the monthly information from an Access DB
for
| my
| > home page. The next step is that I need to pull the next occurence and
| only
| > the next occurence for a particular event and place that in a results
box.
| > Here is what I have so far and FP cannot verify this query:
| >
| > SELECT TOP 1 MonthName([EventMonth],True)+" "+Str([EventDay]) AS Expr1
| > FROM Main
| > WHERE (((Main.EventDate)>=Date()) AND ((Main.EventDetail)="drill"))
| > ORDER BY Main.EventDate;
| >
| >
|
|

Thank you Jon, but this will not verify either, here is a snip of the verify
error details:

[Microsoft][ODBC Microsoft Access Driver] Undefined function 'MonthName' in
expression.

I am going to try something different- I will let youknow the results

Mike


"Jon Spivey" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Mike,
>
> monthname wants the monthnumber (eg 1) not the whole date -
>
> SELECT TOP 1 MonthName(Month(EventMonth),1)+' '+EventDay AS Expr1
> FROM [Main]
> WHERE EventDate >=Date() AND EventDetail='drill'
> ORDER BY EventDate
>
> I've also replaced double quotes with single - Access doesn't mind either
> but it's not a good habit - if you move to another db (eg SQL Server)
you'll
> find you can't use double quotes. Think main is a reserved name too so
I've
> stuck it in [ ] to be on the safe side
>
> Jon
> Microsoft MVP - FP
>
> "Mike Mueller" <(E-Mail Removed)> wrote in message
> news:#DTj#(E-Mail Removed)...
> > Thomas & Jon and all others who are more educated than I at this
> >
> > I have succeeded at pulling the monthly information from an Access DB
for
> my
> > home page. The next step is that I need to pull the next occurence and
> only
> > the next occurence for a particular event and place that in a results
box.
> > Here is what I have so far and FP cannot verify this query:
> >
> > SELECT TOP 1 MonthName([EventMonth],True)+" "+Str([EventDay]) AS Expr1
> > FROM Main
> > WHERE (((Main.EventDate)>=Date()) AND ((Main.EventDetail)="drill"))
> > ORDER BY Main.EventDate;
> >
> >
>
>

Addendum-
I created a new field (EventOccur, text) and used an update query in
Access to do the monthname info. Then I created a NextDate query which just
would return the next event, and I am using this query for the DRW. Works
fine.

Mike


"Mike Mueller" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Thank you Jon, but this will not verify either, here is a snip of the
verify
> error details:
>
> [Microsoft][ODBC Microsoft Access Driver] Undefined function 'MonthName'
in
> expression.
>
> I am going to try something different- I will let youknow the results
>
> Mike
>
>
> "Jon Spivey" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hi Mike,
> >
> > monthname wants the monthnumber (eg 1) not the whole date -
> >
> > SELECT TOP 1 MonthName(Month(EventMonth),1)+' '+EventDay AS Expr1
> > FROM [Main]
> > WHERE EventDate >=Date() AND EventDetail='drill'
> > ORDER BY EventDate
> >
> > I've also replaced double quotes with single - Access doesn't mind
either
> > but it's not a good habit - if you move to another db (eg SQL Server)
> you'll
> > find you can't use double quotes. Think main is a reserved name too so
> I've
> > stuck it in [ ] to be on the safe side
> >
> > Jon
> > Microsoft MVP - FP
> >
> > "Mike Mueller" <(E-Mail Removed)> wrote in message
> > news:#DTj#(E-Mail Removed)...
> > > Thomas & Jon and all others who are more educated than I at this
> > >
> > > I have succeeded at pulling the monthly information from an Access DB
> for
> > my
> > > home page. The next step is that I need to pull the next occurence
and
> > only
> > > the next occurence for a particular event and place that in a results
> box.
> > > Here is what I have so far and FP cannot verify this query:
> > >
> > > SELECT TOP 1 MonthName([EventMonth],True)+" "+Str([EventDay]) AS Expr1
> > > FROM Main
> > > WHERE (((Main.EventDate)>=Date()) AND ((Main.EventDetail)="drill"))
> > > ORDER BY Main.EventDate;
> > >
> > >
> >
> >
>
>