Year of birth from age

[Kuleesha]

Hi,
I have a form which contains the fields "first visit date" (contains today's date in date/time format), age and birth year (as number).
I want to caculate the year of birth by filling the other 2 fields. I wrote the following code for this but it doesn't work.

Private Sub txtAge_AfterUpdate()
    Dim thisyear As Integer
    Dim birthyear As Integer
    Dim age As Integer
    
    thisyear = Year(#Me.txtFirstVisit#)
    age = Me.txtAge
    birthyear = thisyear - age
    
    If Me.txtBirthYear = Null Then
    Me.txtBirthYear = birthyear
    End If

End Sub

I'm an absolute newbie. Please help.

 

[theDBguy]

Hmm, try changing this line to remove the hash tags.

thisyear = Year(Me.txtFirstVisit)

 

[Pat Hartman]

BirthYear = Year(DateAdd(,"yyyy", Me.Age * -1, Date())

Storing Age is redundant and will cause conflicts because it changes over time. Maybe even tomorrow

If you can't store the actual birth date, store just the BirthYear and calculate the age as needed

 

[isladogs]

Agree with previous comments.
You cannot get the Year of Birth from the Age with absolute certainty.
For example two people born on 31 Dec 2000 and 1 Jan 2001 are both currently 20 years old but they have different birth years

 

[Kuleesha]

Above solutions did not work for me.
I know this does not feel like sound practice but I have met some people who know their age but cannot remember their birth year. I want to use this in this situation where I enter age into non-bound text box and it auto-populates the birth year for me which is the value that is stored. I will later use this birth year to calculate the age of the person at each visit. I don't mind a 1 year difference in the the birth year.
I tried chaging my code a bit as follows but still doen't work

Private Sub txtAge_LostFocus()
Dim ThisYear As Integer
Dim BirthYear As Integer
Dim Age As Integer

Age = CInt(Me.txtAge)
ThisYear = Year(Now)
BirthYear = ThisYear - Age

If Me.txtBirthYear = Null Then
Me.txtBirthYear = BirthYear
End If
End Sub

I have attached a smaller version of my database here.
Please help.

 

[plog]

This should be the code you need:

Private Sub txtAge_LostFocus()

If IsNumeric(Me.txtAge) Then Me.txtBirthYear = Year(Date) - Me.txtAge

End Sub

 

[theDBguy]

Oh, I didn't catch this earlier. You can't compare null to anything. Try it this way.

If IsNull(Me.txtBirthYear) Then

 

[Kuleesha]

Oh, I didn't catch this earlier. You can't compare null to anything. Try it this way.

If IsNull(Me.txtBirthYear) Then

Yes this solved the issue. Much appreciated.