Message box when criteria is met

I have a table as a record source for a data entry form.  One of the fields in the table is OrderDate.  When the user enters a new record on the form and the new order date (today's date) is 5 years AFTER the last order date for that particular part number I want a popup message box to appear letting the user know that...

"Please review the order history for this part.  It was last produced (insert date last order date)”

How can I do this?  I know how to do a DMax for the order date but I don't know the syntax for 5 years prior to today's date.
Who is Participating?
Anders Ebro (Microsoft MVP)Connect With a Mentor Microsoft DeveloperCommented:
To get the date from 5 years ago you could use:

Open in new window

Then it would read as:
If Dmax([your expression here])<DateAdd("y",-5,Date()) then

Open in new window

Gustav BrockConnect With a Mentor CIOCommented:
It's not "y" but "yyyy" ...
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
Thank you Gustav. Your absolutely correct. Silly mistake on my part.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.