Solved

Expiry Date automatic according to course date

Posted on 2006-07-10
9
342 Views
Last Modified: 2012-08-13
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 :)

0
Comment
Question by:ajaeclarke
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 17079134
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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17079170
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



0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 17079180
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:ajaeclarke
ID: 17079345
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 :)
0
 
LVL 4

Expert Comment

by:Carl2002
ID: 17079533
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
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 17079594
Ok, I took just a copy/paste

so anything from Jan to Jul is always 31-DEC?

If Month(Me.CBADate) <= 7 Then
    Me.ExpiryDate = DateSerial(Year(Me.CBADate) + 5, 12, 31)
Else
    Me.ExpiryDate = DateSerial(Year(Me.CBADate) + 5, Month(Me.CBADate) + 6, 1) - 1
End if
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17083307
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)



0
 
LVL 1

Author Comment

by:ajaeclarke
ID: 17095692
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!!
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17112228
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?
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question