Calculating a DOB into Years

[Eric]

On one of the forms in my data back there is Patient information, Patient
Name, MRN, and then Date of Birth. I added a field on the form with the
following:
=Date()-[Date of Birth]/365
I was hoping this would show the age of the patient in years (works similar
in Excel) however when I do that it returns a date and time even when I
change the format to General or Standard number. Any suggestions.

 

[scubadiver]

Spreadsheet mentality is a bit lazy.

try this in a query:

Age: DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") >
Format(Date(), "mmdd"), 1, 0)

or in a form:

=DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),
"mmdd"),1,0)

 

[Douglas J. Steele]

Check http://www.mvps.org/access/datetime/date0001.htm at "The Access Web"

 

[James A. Fortune]

On one of the forms in my data back there is Patient information, Patient
Name, MRN, and then Date of Birth. I added a field on the form with the
following:
=Date()-[Date of Birth]/365
I was hoping this would show the age of the patient in years (works similar
in Excel) however when I do that it returns a date and time even when I
change the format to General or Standard number. Any suggestions.

My favorite:

In a query:

SELECT Int(Format(Date(), "yyyy.mmdd") - Format([Date of Birth],
"yyyy.mmdd")) AS Age FROM MyTable;

In VBA:

Dim intAge As Integer
Dim dtBirthDate As Date

dtBirthDate = #5/7/88#
intAge = Int(Format(Date(), "yyyy.mmdd") - Format(dtBirthDate, "yyyy.mmdd"))
MsgBox intAge

=> 20

Note that the .mmdd fractions are not true fractions of years, but their
relative order remains the same as the relative order of the true
fractions of years. Thus the poset (partially ordered set) will produce
exactly the same results as the true year fraction when detecting when
the age will increment.

James A. Fortune
(e-mail address removed)