Solved

Expiry Date automatic according to course date

Posted on 2006-07-10
9
333 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

708 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

21 Experts available now in Live!

Get 1:1 Help Now