Calculate Expiry Date on IssueDate & DOB

[dx101]

I have a microsoft access 2k project attached to SQL Desktop.
The database is for a card issue scheme. I have most of it working but am finding myself out of my depth with the calculation for the expiry date.

I have a form that has the main form [frm_customers] and a subform [frm_cardissues]. The main form is attached to the main table [tbl_customers] and the subform attached to [tbl_cardissues]

The dataentry form works fine and when the user enters the data from the subform I want to auto complete a couple of fields and set the focus to the end of the form.

On the main form [frm_customers] I have the date of birth field [DOB]
On the subform [frm_cardissues] I have fields [ISSUEDATE] AND [EXPIRYDATE]. When the user enters the card type (Under 17's and 17to18) I want to complete the two fields.

The rules for the expiry date are:

The cards are issued to children for discounts to services.

Type1 - up to 15yr old.
Type2 - 16 - 17 yr old.

The cards don't expire according to DOB but the 31 August.

example:

1. A 15 yr olds (born 1 Aug) type1 card will be valid until 31 Aug (so he gets month at 16 on the old card), They then have to get a Type2 card.
2. A 15 yr old (born 1 Sep) type1 card will be valid un 31 Aug the following year (so he gets almost a year at 16 on Type1 card)
Note: Cards have a maximum life of three years (so a 11 year olds card will expire and have to be renewed for the final year).

This is due to how the UK schools decide which year children are allocated. So, basically I want the expiry date (which will always be 31 Aug) to be auto completed allowing the user to write that date on the card without having to manually calculate the expiry.

I am just baffled on how i could achieve this so any advice would be appreciated... Note I want to be able to put this code on the input form so the ExpiryDate field is auto completed.

Thanks for anyone who can make any suggestions. Sorry this question is so long winded...

DX101

 

[dx101]

Private Sub CARDDESC_AfterUpdate()

If IsNull(ISSUEDATE) Then
ISSUEDATE = Date
End If
EXPIRYDATE.SetFocus
End sub

This is as far as I got.... So it autocompletes the ISSUEDATE field and sets focus on the expiry... I want to auto complete the expiry and setfocus on the [Comments] field at the end...

DX101

 

[ritchieroo]

Expiry Dates

Here is my outline on how I would approach the problem (no code sorry)... You should be able to come up with a function that does what you want fairly easily. It will take two parameters: IssueDate and DateOfBirth and return the ExpiryDate. You'll also need a function that can accurately determine age at a specified date (which I can help you with)

Public Function Age(ByVal DateOfBirth As Variant, _
                    Optional ByVal KeyDate As Variant) As Variant

    Dim nGuessAge As Integer

    If IsMissing(KeyDate) Then
        KeyDate = Date
    End If

    If Not (IsDate(DateOfBirth) And IsDate(KeyDate)) Then
        Age = vbDBNull
        Exit Function
    End If

    nGuessAge = DateDiff("yyyy", DateOfBirth, KeyDate)

    Age = nGuessAge + (DateAdd("yyyy", nGuessAge, DateOfBirth) > KeyDate)

End Function

I would start by determining the Issue Date, and then calculate the child's age at 31st August following that date in order to determine what type of card they need.

There are two scenarios for issuing cards: Standard Renewal, where the issue date is 1st September (following expiry of an old card); and First Issue, where the issue date will might be the beginning of term for example.

If the age at the 31st August following is 16 or less, then they need a type one card now. Expiry Date will be that year, and extra years should be added upto your maximum three years depending on age: so a 15yo gets +1 year, a 14yo gets +2 years, 13yo and under get +3 years.

If the age at the 31st August following is 17 or over, then they should get a type two card now. Presumably this expires after 3 years as normal?

 

[dx101]

thanks for the suggestions.... When I get something down worth posting I will post my solution (prob in parts as I work through....)
Comments will be appreciated... I am not a programmer so should be interesting.

Brian