DRW SQL- using a variable

M

Mike Mueller

I am in the process of converting to the use of a dynamic page using a
database for the information. The database has fields of EventDate
(Time/Date field), Event Month-EventDay(Both as numbers) and EventDetail. I
am using asp to determine the current month (CurMonth = Month(Date)). The
trouble I am having is using the variable in the SQL statement. Here is
waht I have for starters...

SELECT Main.EventDate, Main.EventDetail
FROM Main
WHERE (((Main.EventMonth)= &CurMonth &));
 
J

Jon Spivey

Hi,
the database can get the month for you. Assuming SQL means sql server -
SELECT *
FROM Table
WHERE EventMonth = DatePart(month, GetDate())

Jon
Microsoft MVP - FP
 
M

Mike Mueller

Thank you Jon...
I actually am using Access. However, I did remember I can pull the info
from a query and I currently have it set up that way. The next task is
pulling the next date (and only the next date) for a specific task.

Mike
 
J

Jim Buyens

-----Original Message-----
I am in the process of converting to the use of a dynamic
page using a database for the information. The database
has fields of EventDate (Time/Date field),
Event Month-EventDay(Both as numbers) and EventDetail.
I am using asp to determine the current month
(CurMonth = Month(Date)). The trouble I am having is
using the variable in the SQL statement. Here is
waht I have for starters...

SELECT Main.EventDate, Main.EventDetail
FROM Main
WHERE (((Main.EventMonth)= &CurMonth &));
Click to expand...

The correct syntax, as I usually like to format it, is:

sql = "SELECT Main.EventDate, " & _
"Main.EventDetail " & _
"FROM Main " & _
"WHERE (Main.EventMonth = " & CurMonth & ") "

The following, however, seems better, becuase it doesn't
require keeping redundant month column in the database:

sql = "SELECT Main.EventDate, " & _
"Main.EventDetail " & _
"FROM Main " & _
"WHERE Month([EventDate]) = Month(Date()) "

This second form also does away with the need for your
CurMonth ASP variable, although I realize you may need
that for other reasons.

Jim Buyens
Microsoft FrontPage MVP
http://www.interlacken.com
Author of:
*----------------------------------------------------
|\---------------------------------------------------
|| Microsoft Office FrontPage 2003 Inside Out
||---------------------------------------------------
|| Web Database Development Step by Step .NET Edition
|| Microsoft FrontPage Version 2002 Inside Out
|| Faster Smarter Beginning Programming
|| (All from Microsoft Press)
|/---------------------------------------------------
*----------------------------------------------------
 
Top