Using a formula result for later data manipulation

[Guest]

I have a database that takes the birthdate and calculates the age of the
client using the following formula:

=DateDiff("yyyy",[birthdate],Now())+Int(Format(Now(),"mmdd")<Format([Birthdate],"mmdd")))

I did this by inserting the code as a control source property of the 'Age'
field in my database

I subsequently need to use the age value (eg 33,32,18) to calculate
statistics, so I have inserted a new field 'Age2' where I am manually
transferring the value from Age - terribly inefficient practice I know...

Is there any way I can tell my form (Clients) that the value stored in Age
should also be duplicated in Age2 ?

 

[John W. Vinson]

Is there any way I can tell my form (Clients) that the value stored in Age
should also be duplicated in Age2 ?

Simply set the control source of the *UNBOUND* textbox Age2 to the same
expression. Don't store the age in *any* table field; just recalculate it as
needed.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

John W. Vinson [MVP]

 

[Guest]

Thanks JWV, I've had some assistance - and here's a detailed solution:

1. have a public module like this :

Option Compare Database
Option Explicit
Public Function Age(ByVal pvBirthdate As Variant) As Integer
'------------------

If (IsDate(pvBirthdate)) Then
Age = DateDiff("yyyy", pvBirthdate, Now()) + Int(Format(Now(),
"mmdd") < Format(pvBirthdate, "mmdd"))
Else
Age = 0
End If
End Function

2. And then in the form :

=DCount("[Birthdate]","Clients","((Age([Birthdate])>24)and(Age([Birthdate])<45))")

Obviously my solution is tailored to my requirements, this will show me the
total of all clients in the age range of 25-44, using the birthdate field
only.

3. I have modified the client form afterupdate event:

Private Sub Birthdate_AfterUpdate()
Me.Age = DateDiff("yyyy", Birthdate, Now()) + Int(Format(Now(), "mmdd")
< Format(Birthdate, "mmdd"))
End Sub

But this is merely another take on what I was doing earlier, and is only
there as eyecandy for users once they fill out the birthdate field.

Mæl.

 

[John W. Vinson]

=DCount("[Birthdate]","Clients","((Age([Birthdate])>24)and(Age([Birthdate])<45))")

Obviously my solution is tailored to my requirements, this will show me the
total of all clients in the age range of 25-44, using the birthdate field
only.

3. I have modified the client form afterupdate event:

Private Sub Birthdate_AfterUpdate()
Me.Age = DateDiff("yyyy", Birthdate, Now()) + Int(Format(Now(), "mmdd")
< Format(Birthdate, "mmdd"))
End Sub

You can get the exact same result with no function - by using the date of
birth directly. If you put a criterion on Birthdate of

DateAdd("yyyy", -45, Date()) AND < DateAdd("yyyy", -25, Date())

you'll find all people whose birthdates are between 25 and 45 years ago today.

But this is merely another take on what I was doing earlier, and is only
there as eyecandy for users once they fill out the birthdate field.

Just so the Age control is *UNBOUND*. If you're storing the age in your table
today... you can be totally assured that every age in your table will be wrong
a year from now.

John W. Vinson [MVP]

 

[Guest]

Absolutely correct John - as I said, eyecandy only.

=DCount("[Birthdate]","Clients","((Age([Birthdate])>24)and(Age([Birthdate])<45))")

Obviously my solution is tailored to my requirements, this will show me the
total of all clients in the age range of 25-44, using the birthdate field
only.

3. I have modified the client form afterupdate event:

Private Sub Birthdate_AfterUpdate()
Me.Age = DateDiff("yyyy", Birthdate, Now()) + Int(Format(Now(), "mmdd")
< Format(Birthdate, "mmdd"))
End Sub

You can get the exact same result with no function - by using the date of
birth directly. If you put a criterion on Birthdate of

DateAdd("yyyy", -45, Date()) AND < DateAdd("yyyy", -25, Date())

you'll find all people whose birthdates are between 25 and 45 years ago today.

But this is merely another take on what I was doing earlier, and is only
there as eyecandy for users once they fill out the birthdate field.

Just so the Age control is *UNBOUND*. If you're storing the age in your table
today... you can be totally assured that every age in your table will be wrong
a year from now.

John W. Vinson [MVP]