Last day of Month/Last Week of Month

[Guest]

Hello,

I have a bunch of tables that either have daily data (M-F) or weekly data
(usually Fridays) with a date field. I'd like to be able to pull this data
on different frequencies.

For example, for daily data, how can I get the last day of daily data on a
monthly or quarterly basis?
(I'm know that getting a particular day of week is easily solved with
Weekday () command)

Since my weekly data is from Friday's, I also need to get that last Friday
of a month or quarter.

I've seen some posts about getting a particular 3rd weekday of the month,
but I won't know whether the dates I have are the 3rd, 4th, etc of the month.

Your assistance and expertise are greatly appreciated.

Thank you.
kohai

 

[David Seeto via AccessMonster.com]

I would think that you could do this with a series of calculations - it's be
horrible if you tried to do it all int eh one formula, but not so bad if you
break it up into steps:

LastDayOfMonth:
DateSerial([Enter Year],[Enter Month]+1,1)-1

LastWeekDay
Weekday(LastDayOfMonth)

LastFriday:
iif(LastWeekDay>=6,LastDayOfMonth-(LastWeekDay-6),LastDayOfMonth-
(LastWeekDay+1))

 

[Marshall Barton]

I would think that you could do this with a series of calculations - it's be
horrible if you tried to do it all int eh one formula, but not so bad if you
break it up into steps:

LastDayOfMonth:
DateSerial([Enter Year],[Enter Month]+1,1)-1

More common is to use:
DateSerial([Enter Year],[Enter Month]+1,0)

 

[Guest]

Thank you both for your replys. However, I see that the code below will
transform all of the weekly dates to their end-of month date

1/5/90 becomes 1/31/90
1/12/90 " 1/31/90
1/19/90 " 1/31/90
1/26/90 " 1/31/90
2/2/90 " 2/28/90
etc .........
What I'm trying to do is pull only those records from 1/26/90, 2/23/90,
3/30/90 etc. These are the last weekly data points for each month.

I have the same situation with daily data, I'd like to get only the last
daily points for each month (or a quarter)

I apoligise if I did not properly phrase my original question.

Thank you.

I would think that you could do this with a series of calculations - it's be
horrible if you tried to do it all int eh one formula, but not so bad if you
break it up into steps:

LastDayOfMonth:
DateSerial([Enter Year],[Enter Month]+1,1)-1

More common is to use:
DateSerial([Enter Year],[Enter Month]+1,0)

 

[David Seeto via AccessMonster.com]

Well, you could build a separate table containing just those dates you're
interested in, and using a join to get to the data you want. This has the
down side of you having to add the required to this table every now and again.


The other thing you could do is build a query that applies the formula below
to your data table in a GROUP BY - this will give you all of the unique end
data values, and you can then use this query to join with your data table in
the same way that you would a stand alone table. The advantage of this
approach is that you don't have to maintain a separate table, since the query
is updated with the relevant end point as soon as the data table is updated.
The down side is that if your data table doesn't have records for the whole
month, that month won't appear at all in your output.