[AccessD] DatePart Question

Gina,

If I understand what you are asking, then stated a different way would be:
select all records whose date is prior to the first day of this month
(1/1/04) and greater than or equal to the first day of the prior month
(12/1/03).  Your query should consider the full date, not just the month.

Assuming that you always want to key off of today's date, the date of the
first day of this month is:
DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1)

and the date of the first day of the prior month  is:
DateAdd("m",-1,DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1)

Combining them in a where clause for your query would be:

WHERE
((tblTableName.dtmDateFieldName)<DateSerial(DatePart("yyyy",Date()),DatePart
("m",Date()),1) And
(tblTableName.dtmDateFieldName)>=DateAdd("m",-1,DateSerial(DatePart("yyyy",D
ate()),DatePart("m",Date()),1)))

Using less than the first day of the current month and greater than or equal
to the first day of the prior month ensures that the query will work
properly if your field also includes the time.  This should work for any
date/time, even January.

Joe O'Connell

-----Original Message-----
From: Gina Hoopes <hoopesg at hotmail.com>
To: AccessD at databaseadvisors.com <AccessD at databaseadvisors.com>
Date: Friday, January 30, 2004 1:26 PM
Subject: [AccessD] DatePart Question


This may be a dumb question, but here goes.  I have a table full of data
that I need to query, and what I need returned is the data from last month.
If I query the Max on DatePart("m",PeriodEnd) on the data that's there now,
I get a 12 which is correct (from 12/31/03).  But, next month we will have
added January '04 data, so the latest data in the PeriodEnd field will be
1/31/04, but when I query for just the month, am I going to get a 1?  I
guess I'm asking if the Max function is still going to look at the whole
date and return just the DatePart of "m", or will 12 be considered alone,
and therefore greater than 1?

I'm always open to an easier method, if you've got any ideas.

Thanks,
Gina

_________________________________________________________________
High-speed users-be more efficient online with the new MSN Premium Internet
Software. http://join.msn.com/?pgmarket=en-us&page=byoa/prem&ST=1

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com