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)

 

[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.