Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculated Field (2002)

    I have a field called MembershipDate and a field called ExpiryDate. I would like to be able to type in the membership date and have the Expiry Date automatically calclulated for + 1 year from the membership date and then store that date in the expiryDate field in my table. Can someone tell me how to do this.
    Thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculated Field (2002)

    If the expiry date will always be one year later than the membership date, there is no reason to store it in the table, since it is derived information. Instead, you can calculate it in a query:

    ExpiryDate: DateAdd("yyyy", 1, [MembershipDate])

    This ensures that the expiry date is always up-to-date.

    If you want the user to be able to modify the expiry date after it has been filled in automatically, you do need a field for it in the table, and a form based on the table. Say that you have a text box txtMembershipDate bound to the MembershipDate field, and a text box txtExpiryDate bound to the ExpiryDate field on the form. Fill in the expiry date in the After Update event of the membership date text box:

    Private Sub txtMembershipDate_AfterUpdate()
    Me.txtExpiryDate = DateAdd("yyyy", 1, Me.txtMembershipDate)
    End Sub

    If you only want to set the expiry date if it hasn't been filled in yet, modify the code as follows:

    Private Sub txtMembershipDate_AfterUpdate()
    If IsNull(Me.txtExpiryDate) Then
    Me.txtExpiryDate = DateAdd("yyyy", 1, Me.txtMembershipDate)
    End If
    End Sub

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculated Field (2002)

    Thanks Hans, worked great.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •