1. Paste the following function into a standard module in your database:
Public Function GetAge(varDob, Optional varDateAt)
Dim intYears As Integer, intMonths As Integer
' return date at current date if no second argument
' passed into function
If IsMissing(varDateAt) Then varDateAt = Date
If Not IsNull(varDob) Then
' get difference in years
intYears = DateDiff("yyyy", varDob, varDateAt)
' get difference in months
intMonths = DateDiff("m", varDob, _
DateSerial(Year(varDob), Month(varDateAt), Day(varDateAt)))
' adjust result if date of birth falls later in year
' than date at which age to be calculated
If varDateAt < DateSerial(Year(varDateAt), Month(varDob),
Day(varDob)) Then
intYears = intYears - 1
intMonths = intMonths + 12
End If
GetAge = intYears & " year" & _
IIf(intYears <> 1, "s", "") & ", " & _
intMonths & " month" & _
IIf(intMonths <> 1, "s", "")
End If
End Function
2. Save the module under a different name from that of the function, e.g.
mdlDateStuff
3. In your query call the function like so:
SELECT *,
GetAge([DoB]) As Age,
GetAge([Date of Entry]) As [Time in Care],
GetAge([Dob],[Date of Entry]) As [Age at Date of Entry]
FROM Clients;
The age et will be returned in the format #year(s), #month(s).
I've assumed that by "the difference between two" it’s the age at the time
of entry you wish to calculate.
Ken Sheridan
Stafford, England
Jessica said:
What if a child is only a months
Beetle said:
Sorry, I forgot to put () after the word Date in my example, so your query
interpreted it as a field name. Modify it like;
SELECT Clients.*,
DateDiff("yyyy",[BirthDate],Date())+(Date()<DateSerial(Year(Date()),Month([BirthDate]),Day([BirthDate])))
--
_________
Sean Bailey
Jessica said:
SELECT Clients.*,
DateDiff("yyyy",[BirthDate],[Date])+([Date]<DateSerial(Year([Date]),Month([BirthDate]),Day([BirthDate])))
:
You asked essentially the same question two days ago, which was answered.
Here is a repost of the answer if you're having trouble finding it.
***
These calculations should not be done at the table level, so you should
not have fields for clients age and time receiving services in your table.
It is rarely advisable to store calculated values in a table. They should
be done in a calculated field in a query, or in calculated controls on
forms/reports.
An example of an age calculation in a query field would be;
Age: DateDiff("yyyy", [BirthDate], Date) +
(Date < DateSerial(Year(Date), Month([BirthDate]), Day([BirthDate])))
You would then use the query where you would have otherwise used the table.
As far as your time receiving services, you would just calculate the
difference
between the entry date and today's date, or perhaps some type of
ending date field (using the DateDiff function). Again this calculation would
not be stored in your table.
To have the results displayed in years and months, you could use the
Format function.
--
_________
Sean Bailey
:
I have a querie with fields taken from a specific table. The field are DOB,
time receiving services, date of entry and Age.
1. I need the age field to automatically have the age of the child base on
the DOB field and current date.
2. I need the time in care field to automatically enter the date of time in
care base on date of entry and current date.Then the difference between two
dates
all information needs to be specified in year and months
