difficulty creating code

[Charles Moery]

I am having difficulty creating code that will compare a date in one table (or query) in the [Expire] field and subtracting two months from the date and automatically placing the newly calculated date into the table (or query) in the [Notify Expiration] second field. For example:

Expire Notify Expiration

01/01/2007 11/01/2006
02/01/2007 12/01/2006
03/01/2007 01/01/2007
04/01/2007 02/01/2007
05/01/2007 03/01/2007
06/01/2007 04/01/2007
07/01/2007 05/01/2007
08/01/2007 06/01/2007
09/01/2007 07/01 2007
10/01/2007 08/01/2007
11/01/2007 09/01/2007
12/01/2007 10/01/2007

Please note that for the month of January and February, the subtraction is minus two for these two months and the subtraction is minus one for the year. All other subsequent months just subtracts two for the months leaving the year intact.
I would appreciate any help that will progmatically accomplish these calculations.

Thanks

Charles Moery
Keypounder2@aol.com

 

[Jon K]

You can use the DateAdd() function in a query. In query Design View, put the following in the Field: row of a new column.

Notify Expiration: DateAdd("m",-2,[Expire])
.

 

[Charles Moery]

Thanks for the info. I will try your solution this morning and keep my fingers crossed that it works.

 

[RuralGuy]

You are wasting disk space storing a calculated value. Your notify date can *always* be created in a query.

 

[RuralGuy]

Charles,
Jon's suggestion creates a *new* field in your query that gives you the date you need.

In query Design View, put the following in the Field: row of a new column.

Notify Expiration: DateAdd("m",-2,[Expire])