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

    carol333

      Title

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

      Post

      hi

      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

       

      Carol