Data calculation

[DebRogers]

Hi. Thanks for reading my post.


I have a field called IssueDate and another field called ExpiryDate
I need the Expiry date field to look at the date in the IssueDate field, then add six months minus 1 day. The Expiry date is 6 months minus 1 day.
I have the ExpiryDate filed set to a calculated field
When I type DateAdd("m",+6,[IssueDate]) - I get an error message that says The expression dateAdd cannot be used in a calculated column.
So, I have two questions.
1. What type of column do I need the ExpiryDate to be
2. What is the formula I need to add 6 months minus 1 day.

I am using Access 2016 on Windows

Thanks for your help.

Deb

[ranman256]

=DateAdd("d", -1 , DateAdd("m",6, [IssueDate]))

[DebRogers]

Thank you for the amazing formula. When I type it in the expression field I get the error message the the expression cannot be used in a calculated column. Is there a type of column/field I need to use in the table. This is a table - not a query... Thanks again

[CJ_London]

I have the ExpiryDate filed set to a calculated field

the message is clear, you cannot use the DateAdd function in a calculated field. Calculated fields have limited capabilities and can only use a very few functions. It is not recommended to use them, but simply apply the formula in a query whenever it is required.

What type of column do I need the ExpiryDate to be

on the basis that the expiry date is always 6 months less a day, you don't need a field

[DebRogers]

Okay - thank you for your input. I will need to figure out a different way to do what I need to do. It is a medical form that is being produced from my table. The form needs to show the issue date and the expiry date based on this calculation. I can made the formula work in a query, so thank you for that. I just need to figure out how to show the calculated date on the form...

[DebRogers]

okay - I've got it figured out! Thanks everyone!

[MSAccessOldTimer]

DebRogers - can you explain how you accomplished this? I am wanting to do a similar field, except mine [Date2] will be based on adding 2 years to [Date1]. Did you use a calculated field in a Query, or where you able to do it in the table or form?

[Bob Fitz]

DebRogers - can you explain how you accomplished this? I am wanting to do a similar field, except mine [Date2] will be based on adding 2 years to [Date1]. Did you use a calculated field in a Query, or where you able to do it in the table or form?

Use the calculation in the query that is the forms record source.

[MSAccessOldTimer]

Thanks for your quick reply. And for your answer. I will do the same.