Create a field to calcualate a date.

  • Thread starter Thread starter Craig Ferrier
  • Start date Start date
C

Craig Ferrier

I have a list of subscribers that are entered throughout the months. I
would like their subscription to expire on the first day of the following
month on the following year.

Eg. subscribe 03/24/05 - to expire 04/01/06

However if the subscription is within the first week can we then backtrack
the expiry date to the first of the month.

How would I go about building this.

Thanks
Craig
 
Dear Craig:

In organizing this, I would first test whether the date is within the first
7 days of the month. There would be two separate calculations based on
this. This would begin:

IIf(Day(SubscribeDate) <= 7, 12, 13)

Use the DateAdd function to add 12 or 13 months to the SubscribeDate,
depending on whether the above test is true or false. Extract the month and
year from this and use DateSerial to build a date from that Month and Year
along with 1 for the day of the month.

DateAdd("m", SubscribeDate, IIf(Day(SubscribeDate) <= 7, 12, 13))

For a finished procude I have:

DateSerial(Year(DateAdd("m", IIf(Day(CDate(SubscribeDate)) <= 7, 12, 13),
CDate(SubscribeDate))),
Month(DateAdd("m", IIf(Day(CDate(SubscribeDate)) <= 7, 12, 13),
CDate(SubscribeDate))), 1)

Any luck?

Tom Ellison
 
Tom

This works exceptionally well, exactly what I needed.

Let me see if i have this right :
DateSerial (Year) pulls the year from the SubscribeDate + the additional
months.
(Month) the same way - SubscribeDate + 12 or 13
and then CDate is assigning whatever day of the month?
So then DateSerial rebuilds the Month,CDate,Year

Thanks for your response, this will work great for a couple of membership
systems I am working on.

Regards
Craig
 
Dear Craig:

The sequence of events (if you wish to view them that way) is this:

1. The IIf() determines whether to add 12 or 13 months.

2. DateAdd() performs that addition.

3. Year() and Month() extract those parts of the date produced by DateAdd()
in step 2. Note that this means that step 2 must be performed twice, once
for Month() and once for Year().

4. DateSerial reassembles the Month and Year along with the date "1" into a
finished date.

These things happen "inside out" meaning that the IIf() is inside the
DateAdd() and all this is inside both the Month() and Year() functions,
finally assembled in the outermost DateSerial() function.

Tom Ellison
 
Back
Top