After update error when calculating age

[rookiedev]

Hi All-
I have an after update event as such:

Private Sub BirthDate_AfterUpdate()
Me!Age = Year(Date) - Year(Me!BirthDate)
End Sub

Here is the weird thing, the [Age] field will update correctly after you click "End" on the runtime error.

Runtime error 2448
You can't assign a value to this object.

Age is not a tab stop and it is located after BirthDate.
I'm missing something but this worked in Access 97 but is being a pain in 2000.
Can anyone help?
Thanks

RookieDev

 

[VBslammer]

Me!Age = DateDiff("d", Me!BirthDate, Date) \ 365

Integer division ("\") will give you a whole number for Age, if you change it to regular division ("/") it will give you a fractional Age, for ex. 32.75 years.

VBSlammer

Unemployed in Houston, Texas

 

[lupins46]

What is the controlsource value for Age?

 

[MichaelRed]

Also, the "formulas" presented are simplistic and will give incorrect results (statistically) on 50% of cases.

This issus has been discussed in several threads within these (Tek-Tips) fora. I suggest the use of the search capability along with appropiate "key words" to at least get to a correct "formula".





MichaelRed

 

[VBslammer]

Actually, the problems people have discussed about DateDiff only occur if a 2-digit year is used, and since Y2K, most systems have been converted to a 4-digit year. If you use:

Me!Age = Fix(DateDiff("d", "1/7/1900", Date) / 365.245)

you get: 105, so the only caveat is that you have to format your dates like:

strBornDate = Format(Me![dtBorn], "mm/dd/yyyy")

If you're still dealing with dates that could be from 1838 and they use 2-digits years, you're screwed anyway.

VBSlammer

Unemployed in Houston, Texas

 

[MichaelRed]

" ... the problems people have discussed about DateDiff only occur if a 2-digit year ... "

Actually, what I referred to is the inaccuracy which occurs by failing to take into account the month and day of the Date of Birth vs the date for the age to be calculated:

? DAteDiff("YYYY", #8/21/1942#, #8/20/2002#)
 60 

? DAteDiff("YYYY", #8/21/1942#, #8/22/2002#)
 60

Which depicts the obvious.




MichaelRed

 

[gearybish]

Function Age(varDOB As Variant, _
Optional varAsOf As Variant) As Variant
‘Purpose: Return the Age in years.
‘Arguments: varDOB = Date Of Birth
‘varAsOf = the date to calculate the age at, or today if 'missing.
‘Return: Whole number of years.

Dim dtDOB As Date
Dim dtAsOf As Date
Dim dtBDay As Date ‘Birthday in the year of calculation.

Age = Null ‘Initialize to Null

‘Validate parameters
If IsDate(varDOB) Then
dtDOB = varDOB

If Not IsDate(varAsOf) Then ‘Date to calculate age from.
dtAsOf = Date
Else
dtAsOf = varAsOf
End If

If dtAsOf >= dtDOB Then ‘Calculate only if it’s after
'person was born.
dtBDay = DateSerial(Year(dtAsOf), Month(dtDOB), _
Day(dtDOB))
Age = DateDiff(“yyyy”, dtDOB, dtAsOf) + _
(dtBDay > dtAsOf)
End If

End If

End Function

 

[VBslammer]

That's why I use the "d" instead of "yyyy" and divide. The Fix() function just removes the remainders:

? Fix(DateDiff("d", #8/21/1942#, #8/20/2002#) / 365.245)
 59 

? Fix(DateDiff("d", #8/21/1942#, #8/22/2002#) / 365.245)
 60

VBSlammer

Unemployed in Houston, Texas

 

[TheAceMan1]

How are ya rookiedev . . . . .

Hope your still there!

What you show in your post origination is [blue]completely legitimate[/blue]. I ran a large number of simulations in [blue]2000[/blue] (with bound & unbound) and [purple]had no problems.[/purple].

The best solution I can offer at this time, is to delete the [blue]Age[/blue] & [blue]Birthdate fields[/blue] from the form and [blue]reconstitute[/blue] them.

Let me know if any change! . . . . .

See Ya! . . . . . .

 

[rookiedev]

TheAceMan1 and everyone....

My hard drive bit the dust so I've been busy replacing it.
Last day of the warranty too..I had some good luck!

Thanks for the advice I'll give it a try!
I'll let you know what worked!

Thanks


RookieDev

 

[rookiedev]

Lupinis46-
The control source of [Age]is =Age([Birthdate]&""&"Yrs" I get #Name error if the control source is not set as such.
I am really baffled that I get the error message "You can't assign a value to the object" and then it does in deed carry out the operation.

Odd isn't it.

TheAceMan1..I tried to reconstruct the fields but am still getting the same error. Does the control source answer throw anything into the mix?

gearybish-
The rookie that I am you will have to tell me exactly how to use the function that you posted. Do I have to change anything to reflect the names of my fields. It looks good just tell me how to set it up.

Thanks again!

RookieDev

 

[lupins46]

If your controlsource is a calculation then you cannot assign a value to it.

 

[VBslammer]

1. Make sure you rename the textbox to something like "txtAge" because if the textbox has the same name as a field you refer to in the controlsource, it will generate the #Name error.

2. Paste the function in a standard module to give it global scope

3. Reference the function in the controlsource:

=Age([Birthdate]) & " Yrs."

VBSlammer

Unemployed in Houston, Texas

 

[rookiedev]

VBslammer-
I don't know what I did wrong but nothing is happening when I enter a birthdate now. I pasted the value to reference the function but then got a zero in a field named TxtAge. I know I am missing something but what?

RookieDev

 

[rookiedev]

Option Compare Database
Option Explicit
This is a function I already have in my database but I don't know if it is functioning because it worked in 97 and I have issues with it now that I am running 2000. Been so long that it was in place I can't remember how I referenced it in the first place.

It is called ModBirthdateCalc

Function Age(varBirthDate As Variant) As Integer
Dim varAge As Variant

If IsNull(varBirthDate) Then Age = 0: Exit Function

varAge = (DateDiff("yyyy", varBirthDate, Now()))
If Date < DateSerial(Year(Now), Month(varBirthDate), _
Day(varBirthDate)) Then
varAge = varAge - 1
End If
Age = CInt(varAge)
End Function


RookieDev

 

[DEKL]

I've just started having the same problem in Microsoft Access 2000 that you have described. The following code generates runtime error 2448 when it hits the line immediately following the Else:

Private Sub Occupation_AfterUpdate()
If Me.Occupation.Value = 3 Then
Me.OccupationDesc.Enabled = True
Else
Me.OccupationDesc.Value = Null
Me.OccupationDesc.Enabled = False
End If
End Sub

OccupationDesc is a text box whose control source is a text field where nulls and zero length strings are allowed. I have recreated the field, recreated the form from scratch, compacted and repaired the front and back ends, renamed everything, rebuilt the query etc. The weird thing is that this code was working and suddenly stopped working. I'm starting to wonder if there is some kind of corruption in the front end database. Any ideas?