Solved

Expiry Date automatic according to course date

Posted on 2006-07-10
9
334 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
 
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
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…
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…

910 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now