?
Solved

Expiry Date automatic according to course date

Posted on 2006-07-10
9
Medium Priority
?
346 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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 2000 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

800 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