Another DRW SQL statement - Access MDB

M

Mike Mueller

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;
 
J

Jon Spivey

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
 
X

xmas

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
=======================
| 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
|
| | > 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;
| >
| >
|
|
 
M

Mike Mueller

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 said:
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 said:
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
Click to expand...
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;
Click to expand...
Click to expand...
 
M

Mike Mueller

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 said:
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 said:
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 said:
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
Click to expand...
for
my
home page. The next step is that I need to pull the next occurence
Click to expand...
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;
Click to expand...
Click to expand...
Click to expand...
 
Top