datediff

[Chuck]

I'm getting an #Error message when i use this function attached to unbound
control source of text field [Age] on a form:

=DateDiff("yyyy",[Client_DoB],Now())+Int(Format(Now(),"mmdd")<Format([Client_DoB],"mmdd"))

Dob format is dd-mmm-yyyy and Text field is Number (long Integer)
I'm trying to return the current age of the client

 

[fredg]

I'm getting an #Error message when i use this function attached to unbound
control source of text field [Age] on a form:

=DateDiff("yyyy",[Client_DoB],Now())+Int(Format(Now(),"mmdd")<Format([Client_DoB],"mmdd"))

Dob format is dd-mmm-yyyy and Text field is Number (long Integer)
I'm trying to return the current age of the client

Does this work?
As the control source of the [Age] unbound control ....
=DateDiff("yyyy",[Client_DOB],Date())-IIf(Format([Client_DOB],"mmdd")>Format(Date(),
"mmdd"),1,0)

This Age calculated value should NOT be stored in any table.
Any time you need the Age, simply recalculate it, as above.

Note: The Format of the [Client_DOB] field is irrelevant (if it is a
Date datatype field.

 

[Chuck]

I created the table with DoB information from a spreadsheet so some of the
fields have DoB information (dd-mmm-yyyy) and some records are blank (no dob
information).
I just realized that my function is calculating the correct age for a record
where the data was imported with dd-mmm-yyyy format but if i enter the
dd-mmm-yyyy format into a form, that's when i get the error.
What can this be?

I'm getting an #Error message when i use this function attached to unbound
control source of text field [Age] on a form:

=DateDiff("yyyy",[Client_DoB],Now())+Int(Format(Now(),"mmdd")<Format([Client_DoB],"mmdd"))

Dob format is dd-mmm-yyyy and Text field is Number (long Integer)
I'm trying to return the current age of the client

Does this work?
As the control source of the [Age] unbound control ....
=DateDiff("yyyy",[Client_DOB],Date())-IIf(Format([Client_DOB],"mmdd")>Format(Date(),
"mmdd"),1,0)

This Age calculated value should NOT be stored in any table.
Any time you need the Age, simply recalculate it, as above.

Note: The Format of the [Client_DOB] field is irrelevant (if it is a
Date datatype field.

 

[Linq Adams via AccessMonster.com]

I answered this yesterday; don't know where the answer went!

For an expression in a Control Source to work, all elements of the expression
must be available when the record loads. When the DOB is present when the
record loads (when it's inserted from the imported data) everything is cool,
as you've observed. But when the DOB field is blank, you get the #Error.

Since sometimes DOB is initially present and sometimes it's not, you need to
use your formula for calculating age in some event that won't be triggered
until it's present. Do you want the person's age at the time the record is
created, or do you the field to always reflect the current age, even a year
or two from now? You could, for example, place it in the Form_Current event,
making allowances for it to display nothing if a DOB is absent.

I created the table with DoB information from a spreadsheet so some of the
fields have DoB information (dd-mmm-yyyy) and some records are blank (no dob
information).
I just realized that my function is calculating the correct age for a record
where the data was imported with dd-mmm-yyyy format but if i enter the
dd-mmm-yyyy format into a form, that's when i get the error.
What can this be?

I'm getting an #Error message when i use this function attached to unbound
control source of text field [Age] on a form:

[quoted text clipped - 14 lines]

Note: The Format of the [Client_DOB] field is irrelevant (if it is a
Date datatype field.

 

[Chuck]

Ok i understand but the dob for the record in question is there in the table
now, yet when i load the form, it still shows as #Error? Other records are ok
I guess i could trigger the function on Dob not being null. what do you think

I answered this yesterday; don't know where the answer went!

For an expression in a Control Source to work, all elements of the expression
must be available when the record loads. When the DOB is present when the
record loads (when it's inserted from the imported data) everything is cool,
as you've observed. But when the DOB field is blank, you get the #Error.

Since sometimes DOB is initially present and sometimes it's not, you need to
use your formula for calculating age in some event that won't be triggered
until it's present. Do you want the person's age at the time the record is
created, or do you the field to always reflect the current age, even a year
or two from now? You could, for example, place it in the Form_Current event,
making allowances for it to display nothing if a DOB is absent.

I created the table with DoB information from a spreadsheet so some of the
fields have DoB information (dd-mmm-yyyy) and some records are blank (no dob
information).
I just realized that my function is calculating the correct age for a record
where the data was imported with dd-mmm-yyyy format but if i enter the
dd-mmm-yyyy format into a form, that's when i get the error.
What can this be?

I'm getting an #Error message when i use this function attached to unbound
control source of text field [Age] on a form:

[quoted text clipped - 14 lines]

Note: The Format of the [Client_DOB] field is irrelevant (if it is a
Date datatype field.

 

[Chuck]

Sorry Linq but i am not seeing the answer to me question. please answer post
from 10/5/2007. Also the suggestion from fredq did not work either.

Ok i understand but the dob for the record in question is there in the table
now, yet when i load the form, it still shows as #Error? Other records are ok
I guess i could trigger the function on Dob not being null. what do you think

I answered this yesterday; don't know where the answer went!

For an expression in a Control Source to work, all elements of the expression
must be available when the record loads. When the DOB is present when the
record loads (when it's inserted from the imported data) everything is cool,
as you've observed. But when the DOB field is blank, you get the #Error.

Since sometimes DOB is initially present and sometimes it's not, you need to
use your formula for calculating age in some event that won't be triggered
until it's present. Do you want the person's age at the time the record is
created, or do you the field to always reflect the current age, even a year
or two from now? You could, for example, place it in the Form_Current event,
making allowances for it to display nothing if a DOB is absent.

I created the table with DoB information from a spreadsheet so some of the
fields have DoB information (dd-mmm-yyyy) and some records are blank (no dob
information).
I just realized that my function is calculating the correct age for a record
where the data was imported with dd-mmm-yyyy format but if i enter the
dd-mmm-yyyy format into a form, that's when i get the error.
What can this be?

I'm getting an #Error message when i use this function attached to unbound
control source of text field [Age] on a form:
[quoted text clipped - 14 lines]
Note: The Format of the [Client_DOB] field is irrelevant (if it is a
Date datatype field.