Solved

Expiry Date automatic according to course date

Posted on 2006-07-10
9
335 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
  • 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 33

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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

832 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