5 Replies Latest reply on Dec 4, 2009 12:19 AM by carol333

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



      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