AnsweredAssumed Answered

FM 10 - calculating date 2 month's prior to my record dates

Question asked by carol333 on Dec 3, 2009
Latest reply on Dec 4, 2009 by carol333


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


many thanks