Using MS Access 2000, I have three fields of interest on a form:
1. Membership
2. Begin Date
3. End date
We currently enter all three fields by hand - the first with an
number, the second and third with dates. I want to set it up so that
when a number is entered into membership it represents the number of
annual memberships. If a 1 is entered the Begin Date would
automatically enter the current date and the End Date would
automatically enter 1 year from now. If a 1.5 is entered the Begin
Date would automatically enter the current date and the End Date would
automatically enter 18 months from now. This all must be done within the form.
How?

The example code below is based on a form which has three text boxes:
1. txtMembership
2. txtBeginDate
3. txtEndDate

txtBeginDate and txtEndDate are both formatted as "Short Date", but
you can use whatever date format suits you.

I put the example on the OnExit event of txtMembership, but you can
move it to whichever event makes sense for your application.

Private Sub txtMembership_Exit(Cancel As Integer)
' Declare two date variables
Dim datBeginDate As Date
Dim datEndDate As Date

    ' If txtMembership contains one of the specified values
    ' calculate the dates, otherwise, clear the date fields.
    If txtMembership = "1" Then
        datBeginDate = Now()
        datEndDate = DateAdd("yyyy", 1, datBeginDate)
        txtBeginDate = datBeginDate
        txtEndDate = datEndDate
    ElseIf txtMembership = "1.5" Then
        datBeginDate = Now()
        datEndDate = DateAdd("m", 18, datBeginDate)
        txtBeginDate = datBeginDate
        txtEndDate = datEndDate
    Else
        txtBeginDate = ""
        txtEndDate = ""
    End If

End Sub

Note: The Now() function returns the system date and time.
Note: I have used the DateAdd function to perform both calculations.

Additonal Resources:

Look at the Now() and DateAdd functions in the Access 2000 online
Help.

MVPS Access FAQ Date Section
http://www.mvps.org/access/datetime/index.html

Good luck with your Access project!

- Hammer

---

Request for Answer Clarification by sjkistler-ga on 12 Feb 2003 09:14 PST

This is close to what I was seeking, but not exactly it. The number is
not limited to just 1 and 1.5, it could be any value (.5, 2, 6). Then,
that value would feed into the calculation of the appropriate number
of months.

---

Clarification of Answer by hammer-ga on 12 Feb 2003 09:57 PST

Ahhhh, I see. Okay, then use this routine instead. It will accept any
value. The code assumes that the value will always be entered in
years.

Private Sub txtMembership_Exit(Cancel As Integer)
' Declare two date variables
Dim datBeginDate As Date
Dim datEndDate As Date
Dim dblYears As Double
Dim intMonths As Integer

    ' Convert the value entered in txtMembership
    ' into a number. We use a Double because there
    ' may a decimal portion, like 2.5 years.
    dblYears = CDbl(txtMembership)

    ' Convert the value entered in txtMembership
    ' from years into months
    intMonths = dblYears * 12
    
    datBeginDate = Now()
    ' Add the specified number of months to today's date
    datEndDate = DateAdd("m", intMonths, datBeginDate)
    txtBeginDate = datBeginDate
    txtEndDate = datEndDate

End Sub


- Hammer

Well done - responded and refined answer to meet my specs. Thanks.