Help with Date Formula

[mkm612]

I have two date fields in a table...
One field is hard-keyed and the second field has to be 9 months prior to the month of the date in the first field. Thanks!

 

[MichaelintheUK]

Hello mkm612

Why not try a query with a derived or calculated field.

In query design view:

Field date OldDate:OldDate: DateAdd("m",-9,[date])
Table table1
Sort
Show
Criteria

This uses the dateadd function m=months, -9 because you are subtracting rather than adding and the name of the field in square brackets.

Thanks

Michael

 

[mkm612]

Thank you for your suggestion MichaelintheUK...

I am looking for a way to do this in the table...is there any way to put a formula in the default value box?

I've been using the formula =DateAdd("m",-9,[COAExpires]... (COAExpires is the name of the field I'm trying to pull the data from) but for some reason it refuses to recognize that field.

Any way to perform this operation in the table?

 

[GoDawgs]

Is the data being entered directly into the table? If you're using a form then you are in control of the fields, so you can easily update the 9 months before field. If not, and the data is entered directly into the table by the users then I think you're out of luck...the only option would be to have an Update query set up that you can run every time you need or add data to the table. Use forms. Hope that helps.

Kevin