4 Replies Latest reply on Apr 29, 2009 12:44 PM by philmodjunk

    Calculated field based on date error

    vpjohnston11

      Summary

      Calculated field based on date error

      Description of the issue

      I have an issue with a calculated field that I can't work out, and wonder if it might be a bug? Here is my calculation: If (fin_tuitionProtParticipation = "Yes" and fin_dateRegFeePd > "2/27/2009" ; 275 ; 0) The calculation works for every date EXCEPT any date in the first 9 days of February. Anytime we have 2/1/2009 thru 2/9/2009 (anything with a single digit for the day of the month for FEBRUARY only), the result is 275 rather than zero. If I change the date to 2/10/2009 from any of the above dates, the result is correctly reported as zero. If I choose any single digit date in January 2009, the result is correctly reported as zero. Yes, I have confirmed that the first field (fin_tuitionProtParticipation) is set to Yes. As a matter of fact, I did a "Records/Replace field contents" to make sure. I have another calculated field based on the same fin_dateRegFeePd field that has the same problem...anything in the first nine days of February results in the wrong amount result.Hoping someone can explain or fix. File is hosted in FM Server 10 but am working locally in FMPro 9.0v3 on a Mac with OS 10.4.11 

        • 1. Re: Calculated field based on date error
          philmodjunk
            

          is fin_dateRegFeePd a text field or a date field when you look at the field's definition in Manage | Database | Fields? Your description suggests that you've defined it as a text field.

           

          Make sure that it's a date field, then rewrite your expression as:

           

          If (fin_tuitionProtParticipation = "Yes" and fin_dateRegFeePd > getasdate("2/27/2009") ; 275 ; 0)

           

          or

           

          If (fin_tuitionProtParticipation = "Yes" and fin_dateRegFeePd > Date(2; 27; 2009) ; 275 ; 0)

           

          date fields are really number fields that count the number of days since (0/0/0000) with special built in formatting to display in a month day year format. Enclosing text in quotes like you have won't be recognized as a date unless you use a function to convert it.

          • 2. Re: Calculated field based on date error
            vpjohnston11
              

            fin_dateRegFeePd is most definitely a date field.

             

            I have tried it with and without quotes...without is just the latest version of the calculation. It does not seem to matter which I have in there, the result is the same.

             

            I will try what you suggest and let you know. Thanks in advance for the help. 

            • 3. Re: Calculated field based on date error
              vpjohnston11
                

              Ahhhhhhhh.

               

              It worked. (Your second option of Date(2; 27; 2009.) Thanks for the help. Not sure WHY all my calculations all these years have worked without formating the calculation like that. This is the first year in at least 5 years that I have had a problem.

               

              Thanks, again. 

              • 4. Re: Calculated field based on date error
                philmodjunk
                  

                Getasdate("4/1/2009") should also work for you.

                 

                I've used FMP since it was version 2 and a flat file. As best I can remember, "4/4/2009" and 4/4/2009 do not evaluate as a date in a calculation expression for any version of filemaker pro. (The second options is interpreted as 4 divided by 4 divided by 2009 :smileywink: )