Hide Error in Calculated Age Field when no DOB entered?

[scs]

I have the following calculated field in a query. It works great except
when I don't have a Date of Birth entered. Then it displays Err. Is there
a way to display something different or nothing at all? Or is there a way
to display it differently on a form?

Here is the formula I am using:

Age:
IIf(Int(Format([DOB],"y"))>Int(Format(Date(),"y")),DateDiff("yyyy",[DOB],Date())-1,DateDiff("yyyy",[DOB],Date()))

TIA
Steve

 

[Guest]

Hi scs, try:

IIf(Nz([DOB]),"
",IIf(Int(Format([DOB],"y"))>Int(Format(Date(),"y")),DateDiff("yyyy",[DOB],Date())-1,DateDiff("yyyy",[DOB],Date())))

I haven't tested it but in plain language it should read like this:

If DOB is null, then display a zero-length string, else if DOB is greater
than the current date, then perform 1st calculation, else calculate the 2nd
calculation

Let me know the outcome.

 

[Douglas J Steele]

I think you mean IsNull, not Nz.

What you've got would work, but it's backwards: if DOB is null, it'll be
assigned a value of 0 which is False, whereas non-null DOBs would be
non-zero, and hence True.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi scs, try:

IIf(Nz([DOB]),"
",IIf(Int(Format([DOB],"y"))>Int(Format(Date(),"y")),DateDiff("yyyy",[DOB],D
ate())-1,DateDiff("yyyy",[DOB],Date())))

I haven't tested it but in plain language it should read like this:

If DOB is null, then display a zero-length string, else if DOB is greater
than the current date, then perform 1st calculation, else calculate the 2nd
calculation

Let me know the outcome.

I have the following calculated field in a query. It works great except
when I don't have a Date of Birth entered. Then it displays Err. Is there
a way to display something different or nothing at all? Or is there a way
to display it differently on a form?

Here is the formula I am using:

Age:

IIf(Int(Format([DOB],"y"))>Int(Format(Date(),"y")),DateDiff("yyyy",[DOB],Dat
e())-1,DateDiff("yyyy",[DOB],Date()))

TIA
Steve

 

[Guest]

Good catch Douglas! That's what I get for answering questions when I'm
tired.

I think you mean IsNull, not Nz.

What you've got would work, but it's backwards: if DOB is null, it'll be
assigned a value of 0 which is False, whereas non-null DOBs would be
non-zero, and hence True.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi scs, try:

IIf(Nz([DOB]),"
",IIf(Int(Format([DOB],"y"))>Int(Format(Date(),"y")),DateDiff("yyyy",[DOB],D
ate())-1,DateDiff("yyyy",[DOB],Date())))

I haven't tested it but in plain language it should read like this:

If DOB is null, then display a zero-length string, else if DOB is greater
than the current date, then perform 1st calculation, else calculate the 2nd
calculation

Let me know the outcome.

I have the following calculated field in a query. It works great except
when I don't have a Date of Birth entered. Then it displays Err. Is there
a way to display something different or nothing at all? Or is there a way
to display it differently on a form?

Here is the formula I am using:

Age:

IIf(Int(Format([DOB],"y"))>Int(Format(Date(),"y")),DateDiff("yyyy",[DOB],Dat
e())-1,DateDiff("yyyy",[DOB],Date()))

TIA
Steve

 

[scs]

Thanks for the great tip! I appreciate the help. Works perfect. Thanks to
Douglas too.

Hi scs, try:

IIf(Nz([DOB]),"
",IIf(Int(Format([DOB],"y"))>Int(Format(Date(),"y")),DateDiff("yyyy",[DOB],Date())-1,DateDiff("yyyy",[DOB],Date())))

I haven't tested it but in plain language it should read like this:

If DOB is null, then display a zero-length string, else if DOB is greater
than the current date, then perform 1st calculation, else calculate the
2nd
calculation

Let me know the outcome.

I have the following calculated field in a query. It works great except
when I don't have a Date of Birth entered. Then it displays Err. Is
there
a way to display something different or nothing at all? Or is there a
way
to display it differently on a form?

Here is the formula I am using:

Age:
IIf(Int(Format([DOB],"y"))>Int(Format(Date(),"y")),DateDiff("yyyy",[DOB],Date())-1,DateDiff("yyyy",[DOB],Date()))

TIA
Steve