98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Access Training and help » Translating an Excel formula into an Access expression
Translating an Excel formula into an Access expression
Resolved · High Priority · Version 2003
Wed 16 Jun 2010, 15:46Delegate 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:32Trainer 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
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Access tip:Insert The Current TimeTo insert the current time into a Table field or Form textbox use: |
