Calculated field error (1 Viewer)

[corneliud]

In a query I have a calculated field which outputs the age taking into account the date of birth. Sometimes it works and I can see the age in the query sometimes it doesn't work (especially after saving the database) and I get an #Error. How can i overcome this?

Best regards,

Corneliu Dimitriu

 

[theDBguy]

Hi. Welcome to AWF!

My guess would be, when it doesn't work, the birthdate might be missing. If so, you could try using the Nz() function in your calculation.

 

[corneliud]

Hi. Welcome to AWF!

My guess would be, when it doesn't work, the birthdate might be missing. If so, you could try using the Nz() function in your calculation.

The error appears for the whole field. No, none of the birthdays are missing.

 

[arnelgp]

what is your formula:

Age: DateDiff("yyyy", DOBField, Date()) + (DatePart("y", Date()) < DatePart("y", DOBField))

 

[corneliud]

what is your formula:

Age: DateDiff("yyyy", DOBField, Date()) + (DatePart("y", Date()) < DatePart("y", DOBField))

Age: Int(([Date of Oncall]-[Date of Birth])/365.2421897)

 

[The_Doc_Man]

If neither date is missing, then the only other issue is, what data type are those fields? If they are not both "Date" fields but rather one or the other is of type "Short Text" then your error would be a non-conforming input.

Offhand, that formula looks OK because you are not trying to format it as anything other than an integer. Therefore, the hidden "gotcha" situations that crop up in date conversions don't appear to apply here.

 

[arnelgp]

Age: DateDiff("yyyy", Nz([Date of Oncall], Date()), [Date of Birth])

 

[corneliud]

Age: DateDiff("yyyy", Nz([Date of Oncall], Date()), [Date of Birth])

It returns a negative value

 

[arnelgp]

reverse the position of the fields:

Age: DateDiff("yyyy", [Date Of Birth], Nz([Date of Oncall], Date()))

 

[corneliud]

Age: DateDiff("yyyy", [Date Of Birth], Nz([Date of Oncall], Date()))

It works! Thanks a lot!

 

[arnelgp]

you are welcome!