#error in calculate age

I have a field [DOB] for date of birth. Some records are blank in this
field. If non-blank, I want to calculate age, otherwise, blank as follows:

=IIf(IsNull([DOB]),"",DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")Format([DOB],"mmdd"))
& " Yrs")

Record with DOB field is calculated correct age. DOB blank still gives me
#Error. Which part did I do wrong? Thanks.

On Sat, 26 Jul 2008 15:58:58 -0700, "Song" wrote:

Record with DOB field is calculated correct age. DOB blank still gives me
#Error. Which part did I do wrong? Thanks.

Access may be upset about mixing datatypes. The DateDiff function
returns an Integer, the literal "" returns a Text type. Try

=IIf(IsNull([DOB]),Null,DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")Format([DOB],"mmdd"))

--

John W. Vinson/MVP

I tried your method. Blank DOB still produce #error. Non-blank DOB
calculated correctly.

"John W. Vinson/MVP" wrote in message
...
On Sat, 26 Jul 2008 15:58:58 -0700, "Song" wrote:

Record with DOB field is calculated correct age. DOB blank still gives me
#Error. Which part did I do wrong? Thanks.

Access may be upset about mixing datatypes. The DateDiff function
returns an Integer, the literal "" returns a Text type. Try

=IIf(IsNull([DOB]),Null,DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")Format([DOB],"mmdd"))

--

John W. Vinson/MVP

It is possible that your field is not a DateTime field, but is a text
field that stores a string that looks like a date. In that case, you
may need to test for a zero-length string or a null or just test to see
if Access can treat whatever is in the field as a date using the IsDate
function.

IIF(IsDate([Dob]),DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")Format([DOB],"mmdd")),Null)

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===


Song wrote:
I tried your method. Blank DOB still produce #error. Non-blank DOB
calculated correctly.

"John W. Vinson/MVP" wrote in message
...
On Sat, 26 Jul 2008 15:58:58 -0700, "Song" wrote:

Record with DOB field is calculated correct age. DOB blank still
gives me
#Error. Which part did I do wrong? Thanks.

Access may be upset about mixing datatypes. The DateDiff function
returns an Integer, the literal "" returns a Text type. Try

=IIf(IsNull([DOB]),Null,DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")Format([DOB],"mmdd"))


--

John W. Vinson/MVP

That's it! My DOB IS text field. Use your code and it works perfect. Thanks.

"John Spencer" wrote in message
...
It is possible that your field is not a DateTime field, but is a text
field that stores a string that looks like a date. In that case, you may
need to test for a zero-length string or a null or just test to see if
Access can treat whatever is in the field as a date using the IsDate
function.

IIF(IsDate([Dob]),DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")Format([DOB],"mmdd")),Null)

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===


Song wrote:
I tried your method. Blank DOB still produce #error. Non-blank DOB
calculated correctly.

"John W. Vinson/MVP" wrote in message
...
On Sat, 26 Jul 2008 15:58:58 -0700, "Song" wrote:

Record with DOB field is calculated correct age. DOB blank still gives
me
#Error. Which part did I do wrong? Thanks.

Access may be upset about mixing datatypes. The DateDiff function
returns an Integer, the literal "" returns a Text type. Try

=IIf(IsNull([DOB]),Null,DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")Format([DOB],"mmdd"))

--

John W. Vinson/MVP