FM 10 - calculating date 2 month's prior to my record dates
to introduce myself briefly - I work on a database on FM 10 and I'm still learning about calculations etc so not very advanced user - database is on a server; my records are identified by issue number, month and year.
I have a month (text) field and a year (date) field for each record
my problem is - I need to create a calculated field that, for each record, shows the date 2 months prior (this is needed to give a closing date for advertising)
eg if my record has month January and year 2010 (in 2 separate fields)
I need the calculated field to show 1/11/09 (ie first day of the month, 2 months prior to my record's dates)
I have tried using a calculation I called convert month text to date - along the lines of
If (Left(month;3)="Jan"; "1/1";
etc for each month (to give me 1st day of each month)
then building this into another calculation:
Date (Month ((convert month text to date) -2; Day (convert month text to date); Year (convert month text to date)
to give me the date 2 months prior.
it seemed to work OK for a while but I think the fact that the records at the beginning of the year must calculate back to previous year causes problems
Grateful for any help with this - or suggestions about better way to do this