Formula to Calculate Age, as of a specific date, using a variable for the year

[OB7]

My table contains customer information, including name, birthdate, order number, order date, and order year. I would like to create an Access query in Design View that will provide a customer list including each customer’s AGE as March 1 of the order year.





If the order year is hard coded into the Age formula below, it works without any problem:

Age: Int((#3/1/2017#-[birth_date])/365)


However, I want to use the order_yr variable, as follows, but this results in an error:
Age: Int((#3/1/[order_yr])#-[birth_date])/365)

Any suggestions??

Thanks!!!

[Minty]

You have to concatenate the field into the string. Something like

Age : Int((DateValue("3/1/"&[OrderYear]) - [Birth_Date])/365)

[Micron]

IIRC, you can't use variables in a query field and that is what you asked for. You can use other field names, but to make use of a variable, you'd have to call a function or create your sql in code and run it from a procedure. What you posted looks more like field or form control names, so if they are, best to post back explaining what [Order Year] etc. is.

[OB7]

Thank you, Minty! Your suggestion solved my problem!!

However, in contrast to what I had stated in my post, the OrderYear is not in the TABLE but rather is derived from the OrderDate (which IS in the table) by way of a string function within the QUERY. I apologize for the incorrect information! Thus, when I ran the query using your formula, I received the following message:

"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

However, if I first run my query (without an Age formula) as a MakeTable in order to place the OrderYear in the table, and then query THAT table with your Age formula, it works perfectly! If you know of a more efficient way to do this, rather than having to run two queries, please let me know. Otherwise, having to run two queries isn't so bad, and I'm VERY grateful for your solution to my problem!!

[Micron]

I wouldn't consider running 2 queries to get the desired result to be unusual. However, I would consider creating a table first, in order to accomplish the goal, to be a sign of design problems. What are you going to do each time this needs to happen? Delete then recreate the same table? Not good practice, and some would say, a recipe for db bloat at least.

If OrderYear is a calculated field in a query, you can still use it in other query calculations in additional fields, so I'm not understanding what the issue really is.

[Minty]

To re-inforce what Micron has said, in 99% of cases you shouldn't store anything you can calculate. Just like you would't store someones age (as it is a constantly changing value), if your Order_year is simply derived from an order date you can always get that by simply using Year(YourOrderDate)

By always calculating you don't need to worry trying to capture if anyone ever changes a value in the underlying data.

[OB7]

Minty and Micron,

Thank you both for your help! Much appreciated!