Since the persons "age" will constantly be changing, we of course will NOT
store this value, but calculate when needed
So, To display the date of birth, simply build a public function. This
function can then be used in queries, reports, and of course on a form.....
We could perhaps come up with a complex expression that calculates the age,
but since we are likely to need this age calc in MANY places in the
application (eg: reports, or even selecting people who are turn 50 this
year!!).
So, place the following code in one of your standard code modules....
(remember, a standard code module must have a different name then the
functions and code you place inside the code module).
Public Function dtAgeCalc(dtBirthDate As Variant) As Variant
Dim intage As Integer ' age in years
If IsNull(dtBirthDate) Then
' we check for nulls, since this function
' might be used in query, or reports
' if no birthdate (null)...then null
' is returned by this function
Exit Function
End If
intage = DateDiff("yyyy", dtBirthDate, Date)
' has birth for this year passed?..if not, then
' subtract 1 year.....
If Date < DateSerial(Year(Date), Month(dtBirthDate), Day(dtBirthDate))
Then
intage = intage - 1
End If
dtAgeCalc = intage
End Function
With the above, we now can calculate the age anyplace we need in the
application by feeding the birthday...
In your form, simply place a text box control on the form.
Set the data source of the text box as follows
=dtAgeCalc([BirthDate])
Of course, replace birth with the actual name of the birthday control that
you used on your form......
Of course, this text box should not be edited. So, to "gray" out the
control..and not allow the cursor..simply set the "enabled" setting (in the
data tab properties sheet for this control) to no....
For the color to be red, in design mode, lets use conditional formatting...
in form design form mode, simply click on this new date calc text box you
just made (make sure it is highlighted), then .from the menu go
format->conditional formatting...
select "field value is" less then 18
click on the paint bucket......(tiny drop down arrow)..and select the red
color from the color picker.....
ok.....you are done.....
