Missing Calculated Age Values

  • Thread starter Thread starter AGOKP4
  • Start date Start date
A

AGOKP4

Hello,

I used DateDiff("yyyy",[Birth date],Date()) to calculate age from birth date
in a query. However, the records that don't have any birth dates are missing
from the results.

Is there any way to get those missing records? I need to keep track of the
data in spite of missing birthd ates


Thank you
 
One approach might be to embed that DateDiff() calculation within an Nz()
test expression, showing something like "No DOB" when the [BirthDate] is
null.

Remember that a field may be Null, or may hold a zero-length string. To the
human eye, they both look the same. To Access, however... !

Regards

Jeff Boyce
Microsoft Access MVP
 
Hello Jeff,

Thank you for your reply, how do I embed that? Can you give me an example
of the expression.

THANKS!!

Jeff Boyce said:
One approach might be to embed that DateDiff() calculation within an Nz()
test expression, showing something like "No DOB" when the [BirthDate] is
null.

Remember that a field may be Null, or may hold a zero-length string. To the
human eye, they both look the same. To Access, however... !

Regards

Jeff Boyce
Microsoft Access MVP


AGOKP4 said:
Hello,

I used DateDiff("yyyy",[Birth date],Date()) to calculate age from birth
date
in a query. However, the records that don't have any birth dates are
missing
from the results.

Is there any way to get those missing records? I need to keep track of
the
data in spite of missing birthd ates


Thank you
Click to expand...
Click to expand...
 
In a query, something like (untested):

IIF(Nz([BirthDate],"")="","No DOB", DateDiff("yyyy",[Birth
date],Date()))

Regards

Jeff Boyce
Microsoft Access MVP



AGOKP4 said:
Hello Jeff,

Thank you for your reply, how do I embed that? Can you give me an example
of the expression.

THANKS!!

Jeff Boyce said:
One approach might be to embed that DateDiff() calculation within an Nz()
test expression, showing something like "No DOB" when the [BirthDate] is
null.

Remember that a field may be Null, or may hold a zero-length string. To
the
human eye, they both look the same. To Access, however... !

Regards

Jeff Boyce
Microsoft Access MVP


AGOKP4 said:
Hello,

I used DateDiff("yyyy",[Birth date],Date()) to calculate age from birth
date
in a query. However, the records that don't have any birth dates are
missing
from the results.

Is there any way to get those missing records? I need to keep track of
the
data in spite of missing birthd ates


Thank you
Click to expand...
Click to expand...
Click to expand...
 
Hello Jeff,

I tried the expression you sent but for some reason it didn't work, i was
just meant to copy paste it right?

Regards,
AGOKP4

Jeff Boyce said:
In a query, something like (untested):

IIF(Nz([BirthDate],"")="","No DOB", DateDiff("yyyy",[Birth
date],Date()))

Regards

Jeff Boyce
Microsoft Access MVP



AGOKP4 said:
Hello Jeff,

Thank you for your reply, how do I embed that? Can you give me an example
of the expression.

THANKS!!

Jeff Boyce said:
One approach might be to embed that DateDiff() calculation within an Nz()
test expression, showing something like "No DOB" when the [BirthDate] is
null.

Remember that a field may be Null, or may hold a zero-length string. To
the
human eye, they both look the same. To Access, however... !

Regards

Jeff Boyce
Microsoft Access MVP


Hello,

I used DateDiff("yyyy",[Birth date],Date()) to calculate age from birth
date
in a query. However, the records that don't have any birth dates are
missing
from the results.

Is there any way to get those missing records? I need to keep track of
the
data in spite of missing birthd ates


Thank you
Click to expand...
Click to expand...
Click to expand...
 
No, that comment in parentheses (untested), means I haven't tested it.
You'll need to try it and adjust as needed.

Out of curiosity, WHERE did you try it?

Regards

Jeff Boyce
Microsoft Access MVP


AGOKP4 said:
Hello Jeff,

I tried the expression you sent but for some reason it didn't work, i was
just meant to copy paste it right?

Regards,
AGOKP4

Jeff Boyce said:
In a query, something like (untested):

IIF(Nz([BirthDate],"")="","No DOB", DateDiff("yyyy",[Birth
date],Date()))

Regards

Jeff Boyce
Microsoft Access MVP



AGOKP4 said:
Hello Jeff,

Thank you for your reply, how do I embed that? Can you give me an
example
of the expression.

THANKS!!

:

One approach might be to embed that DateDiff() calculation within an
Nz()
test expression, showing something like "No DOB" when the [BirthDate]
is
null.

Remember that a field may be Null, or may hold a zero-length string.
To
the
human eye, they both look the same. To Access, however... !

Regards

Jeff Boyce
Microsoft Access MVP


Hello,

I used DateDiff("yyyy",[Birth date],Date()) to calculate age from
birth
date
in a query. However, the records that don't have any birth dates
are
missing
from the results.

Is there any way to get those missing records? I need to keep track
of
the
data in spite of missing birthd ates


Thank you
Click to expand...
Click to expand...
Click to expand...
 
Back
Top