asked on

Expiry Date automatic according to course date

Hi Experts

I am having a bit of trouble sorting this one out. When I first designed this database I had the expiry date automatically display to the end of the month + 5 years by applying the following code:

Me.ExpiryDate = DateSerial(Year(Me.CBADate) + 5, Month(Me.CBADate) + 1, 1) - 1

This worked great...however now they have changed the rules..(I hate that!!) and now it needs to be the following:

> For those who attend between January to July the card expiry date is 31/12/ present year + 5

> For those who attend a course in:

August the card expiry date is 31/01/ new year + 5

September the card expiry date is 28/02/ new year + 5

October the expiry date is 31/03/ new year + 5

November the expiry date is 31/04/ new year + 5

December the expiry date is 31/05/ new year + 5

I just cannot get my head around it and appreciate any ideas that anyone has!!

Thankyou in advance :)

rockiroads

Well one big IF? or in vba
I assume new year means following year?
e.g.

public function DetermineExpiryDate(byval dCBADate as date) as Date

if Month(sCBADate) >= 1 and Month(sCBADate) <= 7 then
DetermineExpiryDate = DateSerial(Year(sCBADate)+5,12,31)

elseif Month(sCBADate) = 8 Then
DetermineExpiryDate = DateSerial(Year(sCBADate)+6,2,28)

elseif Month(sCBADate) = 10 Then
DetermineExpiryDate = DateSerial(Year(sCBADate)+6,3,31)

elseif Month(sCBADate) = 11 Then
DetermineExpiryDate = DateSerial(Year(sCBADate)+6,4,31)

else
DetermineExpiryDate = DateSerial(Year(sCBADate)+6,12,31)

elseif Month(sCBADate) = 8 The
DetermineExpiryDate = DateSerial(Year(sCBADate)+6,12,31)

elseif Month(sCBADate) = 8 The
DetermineExpiryDate = DateSerial(Year(sCBADate)+6,5,31)
end if
end sub

You can use case instead of IFs

but a better way would surely be to use DateAdd possibly

rockiroads

Something like this perhaps

Public Function DetermineExpiryDate(ByVal dCBADate As Date) As Date

Dim dDate As Date

If Month(dCBADate) <= 7 Then
DetermineExpiryDate = DateSerial(Year(dCBADate) + 5, Month(dCBADate) + 1, 1) - 1
Else
DetermineExpiryDate = DateSerial(Year(dCBADate) + 5, Month(dCBADate) + 6, 1) - 1
End If
End Function

I guess u dont have to call a function, just do it in your code

If Month(me.CBADate) <= 7 Then
Me.ExpiryDate = DateSerial(Year(Me.CBADate) + 5, Month(Me.CBADate) + 1, 1) - 1
Else
Me.ExpiryDate = DateSerial(Year(Me.CBADate) + 5, Month(Me.CBADate) + 6, 1) - 1
End if

Mike Eghtebas

replace:

Me.ExpiryDate = DateSerial(Year(Me.CBADate) + 5, Month(Me.CBADate) + 1, 1) - 1

with:

Me.ExpiryDate = fnExpiryDate(Me.CBADate)

Where, in a module, you will have:
(assuming CBADate is attendance date)

Function fnExpiryDate(dteTemp as date) As Date

Dim strMonth as string
Dim dte_Temp as date
Dim intYear as integer

strMonth = Format(dteTemp,"mmm")
intYear =Format(dteTemp, "yyyy)

Select case strMonth

case "AUG"
dte_Temp=cdate("31/01/" & (intYear+6)) '1 for new year (next year) + 5 more years

case "SEP"
dte_Temp=cdate("28/02/" & (intYear+6))

case "OCT"
dte_Temp=cdate("31/03/" & (intYear+6))

case "NOV"
dte_Temp=cdate("31/04/" & (intYear+6))

case "DEC"
dte_Temp=cdate("31/05/" & (intYear+6))
case else
'what about the other months? would the existing work for you?
dte_Temp= DateSerial(Year(dte_Temp) + 5, Month(dte_Temp) + 1, 1) - 1
End select

fnExpiryDate = dte_Temp

End Function

mike

ajaeclarke

ASKER

Thanks for the replies.

Rockiroads I am using the following code you supplied:

If Month(me.CBADate) <= 7 Then
Me.ExpiryDate = DateSerial(Year(Me.CBADate) + 5, Month(Me.CBADate) + 1, 1) - 1
Else
Me.ExpiryDate = DateSerial(Year(Me.CBADate) + 5, Month(Me.CBADate) + 6, 1) - 1
End if

and it is working great....one thing though....if it is <=7 I need it to be 31/12/ present year + 5 i.e. CBA Date is 01/07/06 then expiry date would be 31/12/11. At the moment it is coming up as 31/07/10. What part of the code do I change to accomplish this?

Thanks :)

Carl2002

Change
Me.ExpiryDate = DateSerial(Year(Me.CBADate) + 5, Month(Me.CBADate) + 1, 1) - 1
To
Me.ExpiryDate = DateSerial(Year(Me.CBADate) + 6, Month(Me.CBADate) + 1, 1) - 1

ASKER CERTIFIED SOLUTION

rockiroads

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GRayL

First your original code should be simplified:

Me.ExpiryDate = DateSerial(Year(Me.CBADate) + 5, Month(Me.CBADate) + 1, 0)

Then try this:

MeExpiryDate=DateSerial(year(Me!CBAdate)+5,13 + iif(month(Me!CBAdate)<8,0,month(Me!CBAdate)-7),0)

ajaeclarke

ASKER

Hi All

Sorry for delay in getting back to you....I used Rockiroads code and was doing some test and it works great.

Thanks alot for everyone's help...EE saved the day yet again!!

GRayL

My ISP recently put all EE posts into a junk folder and I have been 'out of the loop' for 3 days. I am curious, did my solution even work?