Calculated expiration dates based on a field with no of extensions

[lite4d]

I'm trying to calculate expiration dates based on an original expiration date then if it can be extended based on the number in a field use that number then it will add the amount of months in another field and add it to the original expiration date. Here is what i have so far that doesn't work

Expiration: IIf([Total Shelf life]![no of Extensions]=1,DateAdd("m",[extension 1],[shelf life exp]))

 

[jdraw]

Can you add some context?
What exactly is the field name of original expiration date?
What is the time interval (months/days/weeks..) of an extension?

 

[Minty]

Your table/field syntax is not correct. Try something like

CalcExpiration: IIf([Total Shelf Life].[no of Extensions] >=1, DateAdd("m",[Extension_1],[shelf life exp]),[shelf life exp])

This assumes that [Total Shelf Life] is the name of the table that [No of extensions] lives in?

Also, all those spaces in the field names table names make writing this stuff a nightmare. Field and table names should only have normal characters and possibly underscores in them, never spaces or special characters.

 

[lite4d]

jdraw, the original expiration date is (shelf Life Exp). I have a field that has the number of extensions(up to 3) and fields for the months that i can be extended for each extension.(18mo for first,12mo for second, 6mo for third extension). I need to take the shelf life expiration date and determine then number of extensions and the months those extensions are and come up with a new date for each expiration date. I have fields for each new date so I can do them one at a time.

Minty, that gave me a date but not for the number of months that it can be extended.

Thanks for the help!

 

[Isaac]

jdraw, the original expiration date is (shelf Life Exp). I have a field that has the number of extensions(up to 3) and fields for the months that i can be extended for each extension.(18mo for first,12mo for second, 6mo for third extension). I need to take the shelf life expiration date and determine then number of extensions and the months those extensions are and come up with a new date for each expiration date. I have fields for each new date so I can do them one at a time.

Minty, that gave me a date but not for the number of months that it can be extended.

Thanks for the help!

You forgot the second part of the IIF.
Remember the IIF is "if this is true, then this, else that".
You forgot the "else that" portion.

 

[CJ_London]

actually in access sql you can leave the else parameter out - it returns null if not specified.

also be interested to know what 'doesn't work' means

 

[jdraw]

As mentioned already, suggest you remove embedded spaces in field names.
If the number of extensions are always in 6 month increments, then an expression such as

ExpirationDate : DateAdd("m", NumberExtensions * 6, [TotalShelfLife].[ShelfLifeExp])

may be sufficient.

 

[Pat Hartman]

Start by answering the question - do you need to keep history showing each extension?
If you don't, then you would use this code in your form when the user chooses to extend and specifies the number of months. Always keep the start date separate regardless of whether you need to maintain history or not.