Subtracting Months

Hello all,

I have a report with column headings that are dates.  These are displayed in 
mm-yy format.  The first column is todays date =date().  The next 11 columns 
need to display the previous 11 months.  I had set it up as the second column 
being =date()-30 and the next column =date()-60, and so on and so forth.  The 
problem is (which you may have already figured out) is that when we get to a 
certain point of the month and on certain months 30 days doesnt necessarilly 
equate to the previous month (such as on Jan 31st).  In that event, I would 
have two Jan-08 columns (which I do not want).

Do any of you have a solution to this problem?

Thank you in advance!!!




0
Utf
1/31/2008 7:41:01 PM
access.reports 4434 articles. 0 followers. Follow

2 Replies
747 Views

Similar Articles

[PageSpeed] 28

Use the DateAdd function like this ---
     DateAdd("m", -1, Date()-Day(Date())+1)
     DateAdd("m", -2, Date()-Day(Date())+1)
     DateAdd("m", -3, Date()-Day(Date())+1)
     DateAdd("m", -4, Date()-Day(Date())+1)
  etc.
Day(Date()) return the day of month - today 31.
Date()-Day(Date()) subtract today's number from today resulting in last day 
of last month.
Date()-Day(Date())+1 adds one day to give the first of the month.

-- 
KARL DEWEY
Build a little - Test a little


"REGREGL" wrote:

> Hello all,
> 
> I have a report with column headings that are dates.  These are displayed in 
> mm-yy format.  The first column is todays date =date().  The next 11 columns 
> need to display the previous 11 months.  I had set it up as the second column 
> being =date()-30 and the next column =date()-60, and so on and so forth.  The 
> problem is (which you may have already figured out) is that when we get to a 
> certain point of the month and on certain months 30 days doesnt necessarilly 
> equate to the previous month (such as on Jan 31st).  In that event, I would 
> have two Jan-08 columns (which I do not want).
> 
> Do any of you have a solution to this problem?
> 
> Thank you in advance!!!
> 
> 
> 
> 
0
Utf
1/31/2008 8:06:00 PM
Thank you very much!  That fixed the problem!

"KARL DEWEY" wrote:

> Use the DateAdd function like this ---
>      DateAdd("m", -1, Date()-Day(Date())+1)
>      DateAdd("m", -2, Date()-Day(Date())+1)
>      DateAdd("m", -3, Date()-Day(Date())+1)
>      DateAdd("m", -4, Date()-Day(Date())+1)
>   etc.
> Day(Date()) return the day of month - today 31.
> Date()-Day(Date()) subtract today's number from today resulting in last day 
> of last month.
> Date()-Day(Date())+1 adds one day to give the first of the month.
> 
> -- 
> KARL DEWEY
> Build a little - Test a little
> 
> 
> "REGREGL" wrote:
> 
> > Hello all,
> > 
> > I have a report with column headings that are dates.  These are displayed in 
> > mm-yy format.  The first column is todays date =date().  The next 11 columns 
> > need to display the previous 11 months.  I had set it up as the second column 
> > being =date()-30 and the next column =date()-60, and so on and so forth.  The 
> > problem is (which you may have already figured out) is that when we get to a 
> > certain point of the month and on certain months 30 days doesnt necessarilly 
> > equate to the previous month (such as on Jan 31st).  In that event, I would 
> > have two Jan-08 columns (which I do not want).
> > 
> > Do any of you have a solution to this problem?
> > 
> > Thank you in advance!!!
> > 
> > 
> > 
> > 
0
Utf
1/31/2008 9:36:01 PM
Reply:

Similar Artilces: