using TODAY() in MS Query?

R

rachael

I have a query built in MS Query. I'd like to set it up so it returns results
only from the previous month (e.g., for this month it would only return
results from January). I tried to set the criteria for the date field to
EOMONTH(TODAY(),-2) AND <= EOMONTH(TODAY(),-1) but MS query does not
Click to expand...
recognize the TODAY() function. Is there something else i can use?

rachael
 
R

Ron Coderre

MS Query uses Date() instead of Today()

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
R

rachael

I tried >EOMONTH(DATE(),-2) AND <=EOMONTH(DATE(),-1) and get an error that
"'Date' is not a recognized function name."

rachael
 
R

Ron Coderre

Try experimenting with this criteria:

Between dateserial(year(date()),(month(date())-1),1) And
dateserial(year(date()),(month(date())),0)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
R

rachael

Nope, still doesn't work, because MS Query gives me an error that "'date' is
not a recognized function name."

Do you know of something i could use that doesnt use "date"?

thanks
rachael
 
R

Ron Coderre

The Date() function has always worked fine in MS Query for me.

The criteria SQL I posted returns records with dates between 01/01/2006 and
01/31/2006, inclusive, when the current month is Feb 2006.

There must be something else causing the problem.
What kind of database are you querying?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
R

rachael

I'm querying a sql server. Does it matter that the field contains both a date
and a time stamp?

rachael
 
R

Ron Coderre

I haven't used SQL Server for quite some time and I'm sure there are syntax
differences between Oracle (which I work with) and SQL Server.

For instance, in Oracle, the current date is "SysDate" and
ADD_MONTHS(date,count) performs month arithmetic.

Check the SQL Server reference manual for the proper syntax for working with
dates.

Another thought is to check the MS Access newsgroup.

Sorry I can't be more help on this one.

***********
Regards,
Ron

XL2002, WinXP-Pro
 
Top