Formula returns with #Error everytime Access is opened

[aliaslamy2k]

Dear Experts,



I have create a a form from a Query. In query design view i have entered a formula to calculate Age, Passport expiry days remaining and other few similar formulas. However, when i close the and open the access, the cell in form automatically changes to #Error.

Procedures i have to follow to get it back.

1. Go to Query Design view
2. Click on the Age Field
3. Click Zoom and then click OK inside zoom

Formulas i used in my DB

AGE: DateDiff("yyyy",[DATE_OF_BIRTH],Date())-IIf(Format([DATE_OF_BIRTH],"mmdd")>Format(Date(),"mmdd"),1,0)




PPT_EXP DAYS: DateDiff("d",Date(),[PP EXPIRY])


QID_Exp_Remining_Days: DateDiff("d",Date(),[VISA/QID_EXPIRY])


HC_COUNTDOWN: DateDiff("d",Date(),[HC_EXPIRY])



Once above is done, then the #Error changes to a number as value (i.e. Example Age 35). I am not sure why this happens everytime.
Below is the screenshots for your reference.

Please suggest me if i am doing something wrong in my formula.

Kind regards,
AB

[Cottonshirt]

I don't think you need to Format() the two dates in the IIF() statement. normally, Format() is only used to prepare a date for viewing by a person, if no one will ever see it then formatting a date serves no purpose at all.

in your expression, you have a year in the first part from which you are subtracting a date in mmdd format, which I suspect confuses ACCESS almost as much as it confuses me.

try this: IIF([dateofbirth]>Date(),1,0) which works on my system.


good luck with your project,


Cottonshirt

[aliaslamy2k]

Hi CottonShirt,

I tried your formula, but it returns the value as zero. Please see attached screenshots.

[June7]

I agree that usually should not use Format() function when calculating with full date value (unless you have to deal with international dates http://allenbrowne.com/ser-36.html). Format() returns a string value.

You need to use suggested expression instead of the IIf() in yours.

AGE: DateDiff("yyyy",[DATE_OF_BIRTH],Date()) - IIf([dateofbirth]>Date(),1,0)

However, would a date of birth ever be greater than current date?

[aliaslamy2k]

Hello June7,

Thank you so much, Its working now.

I am new to access formulas and trying hard to learn as much as i could with the help of this Forum.

Answer to your question is : DOB cannot be greater than current date. Apologize for my misunderstanding.

The formula is working perfectly but i still don't understand how this formula works. Why we have to use " - IIf([dateofbirth]>Date(),1,0)" and what does 1,0 stands for.

Kind regards,
AB

[June7]

IIf() function syntax in simple English is:

IIf(expression, return this value if expression true, else return this value)

1 and 0 are just values.

[CJ_London]

Why we have to use " - IIf([dateofbirth]>Date(),1,0)" and what does 1,0 stands for.

you don't - the original formula was this

-IIf(Format([DATE_OF_BIRTH],"mmdd")>Format(Date(),"mmdd"),1,0)

it is basically subtracting 1 from the age by looking at the months and days of the DOB v date

a simpler and more efficient formula would be

+(Format([DATE_OF_BIRTH],"mmdd")>Format(Date(),"mmdd"))


So say DOB is 1st March 2000 and today is 12th May 2020

your formula

DateDiff("yyyy",[DATE_OF_BIRTH],Date()) returns 20 which is correct

but if the DOB is 1st June 2000, it also returns 20 - but that birthday hasn't happened yet - they are still 19

so you need to deduct 1 if the birthday has not yet occurred this year - hence the formula

+(Format([DATE_OF_BIRTH],"mmdd")>Format(Date(),"mmdd"))

this returns a boolean value of either true or false - true is represented by -1, false is represented by 0

so the whole formula

DateDiff("yyyy",[DATE_OF_BIRTH],Date())+(Format([DATE_OF_BIRTH],"mmdd")>Format(Date(),"mmdd"))

DOB=1st March................20..................+........ .............................0 = 20
DOM=1st June.................20..................+.....................................-1 = 19

[Gicu]

I don't think the new formula would give you the same results as your original one. I think in your original one you were trying to subtract one year from the number of years returned by DateDiff if the dateof birth for the current year falls in a month following the current one. And that will never happen (as you noticed in post #3) with IIf([dateofbirth]>Date(),1,0) as it will always return 0. You can use the DateSerial function(https://docs.microsoft.com/en-us/off...erial-function) to build this year's birthdate and compare that to Date():

IIf(DateSerial(Year(Date()),Month([DATE_OF_BIRTH]),Day([DATE_OF_BIRTH])) >Date(),1,0)

Or you can try to use your original formula but put it straight into the control sources of the textboxes that are giving you trouble.

Cheers,