Translating an Excel formula into an Access expression

Resolved · High Priority · Version 2003

Wed 16 Jun 2010, 15:46 Delegate Pasquale said...

Pasquale has attended:
Access Introduction course
Access Intermediate course
Access Advanced course

Translating an Excel formula into an Access expression

Hello,

I've got a spreadsheet that contains a list of birth dates of several people on a database I manage. In the cases where I don't have a date of birth, the date is listed as "unknown." I need to calculate all ages (as an integer value) and have come up with the following formula in Excel to do that:

=IF((AND(F4="unknown")),"unknown", (INT((TODAY()-F4)/365.25)))

How do I translate that formula into an Access expression that can be run as a query?

Thu 17 Jun 2010, 13:32 Trainer Simon said...

RE: Translating an Excel formula into an Access expression

Hi Pasquale,

Thank you for your question.

This formula should do the trick:

age: IIf([DateOfBirth]="Unknown","Unknown",DateDiff("yyyy",[DateOfBirth],Date()))

Create a query add the fields you need and then add a new field by using the expression builder and copy the formula above into the box and change the field as necessary.

I hope this helps.

Regards

Simon