Summarize Date as MonthEnding Date 1

[shelby55]

Hello

I am using Access 2002.

I have data at the patient visit level (one line of data is one abstract) and want to create a query to link with other data. The other data query is cumulative data with the "month ending" value as Sept 30, 2010, August 31, 2010 etc.

The data I have has all dates but how would I create a column showing month ending to group on i.e. if the date is Apr 1 thru Apr 30 then I want it to show as Apr 30, 2010, May 1 thru 31 will be May 31, 2010?

I was thinking of creating a table with all dates and a secondary month ending column to link to but this isn't a static database and I don't want to have to keep updating the dates table...is there a way to do programatically?

Thanks.

 

[PHV]

month ending: DateSerial(Year([yourDateField]),1+Month([yourDateField]),0)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shelby55]

Hi

You rock, PHV, thanks so very much. Worked like a charm!