#error in calculate age

[Song]

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.

 

[John W. Vinson/MVP]

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"))

 

[Song]

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

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 Spencer]

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
'====================================================

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

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"))

 

[Song]

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

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
'====================================================

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

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"))