[AccessD] DatePart Question

Gina Hoopes hoopesg at hotmail.com
Fri Jan 30 14:03:59 CST 2004


Thank you, Joe. I think that sounds much better than what I was using.

Gina


From: "Joseph O'Connell" <joconnell at indy.rr.com>
To: <AccessD at databaseadvisors.com>, <hoopesg at hotmail.com>
Subject: Re: [AccessD] DatePart Question
Date: Fri, 30 Jan 2004 14:26:45 -0500

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

_________________________________________________________________
Find high-speed ‘net deals — comparison-shop your local providers here. 
https://broadband.msn.com



More information about the AccessD mailing list