4 Replies Latest reply on Dec 8, 2011 9:41 AM by tcwaters

    Using a date field in a If statement

    tcwaters

      Can you please show me what I am doing wrong here. I have a calc that is used to populate a field when data is imported. Field = "Degree"

       

      Case ( cdegrcode = 1 ; "AA" ;

      cdegrcode = 2 ; "AS" ;

      cdegrcode = 3 ; "BA" ;

      cdegrcode = 4 ; "OTHER BACHELORS" ;

      cdegrcode = 5 ; "BS" ;

      cdegrcode = 6 ; "CERTIFICATE" ;

      cdegrcode = 7 ; "DOC CHIROPRACTIC" ;

      cdegrcode = 8 ; "DOC OF OESTEOPATHIC SCIENCES" ;

      cdegrcode = 9 ; "OTHER DOCTORATE" ;

      cdegrcode = 10 ; "DOC DENTISTRY ";

      cdegrcode = 11 ; "DOC VET" ;

      cdegrcode = 12 ; "JD" ;

      cdegrcode = 13 ; "MA" ;

      cdegrcode = 16 ; "MS" ;

      cdegrcode = 17 ; "NO DEGREE" ;

      cdegrcode = 18 ; "PHD" ;

      "No Degree" )

       

      This works fine.

       

      What I need to do however, is make this entry based on the date of the degree (field="cdatedegre"

       

      This is the part that isn't working:

      If ( cdatedegre > 9/1/2012 ; "Too Late" ; "Some other choice" )

       

      What I expected to do was:

      If ( cdatedegre > 9/1/2012 ; "Too Late" ;

      Case ( cdegrcode = 1 ; "AA" ;

      cdegrcode = 2 ; "AS" ;

      cdegrcode = 3 ; "BA" ;

      cdegrcode = 4 ; "OTHER BACHELORS" ;

      cdegrcode = 5 ; "BS" ;

      cdegrcode = 6 ; "CERTIFICATE" ;

      cdegrcode = 7 ; "DOC CHIROPRACTIC" ;

      cdegrcode = 8 ; "DOC OF OESTEOPATHIC SCIENCES" ;

      cdegrcode = 9 ; "OTHER DOCTORATE" ;

      cdegrcode = 10 ; "DOC DENTISTRY ";

      cdegrcode = 11 ; "DOC VET" ;

      cdegrcode = 12 ; "JD" ;

      cdegrcode = 13 ; "MA" ;

      cdegrcode = 16 ; "MS" ;

      cdegrcode = 17 ; "NO DEGREE" ;

      cdegrcode = 18 ; "PHD" ;

      "No Degree" )

      )

       

      I have tried the If statement with the date in and out of "".

       

      What do I have wrong with If ( cdatedegre > 9/1/2012

       

      Thx,

        • 1. Re: Using a date field in a If statement
          sporobolus

          on 2011-12-07 13:19 user2259 wrote

          What do I have wrong with If ( cdatedegre>  9/1/2012

           

           

          FileMaker sees 9/1/2012 as a division expression; assuming cdatedegre is a date

          field, try

           

          if (cdatedegre > date (9;1;2012) ...

          1 of 1 people found this helpful
          • 2. Re: Using a date field in a If statement
            RayCologon

            user2259 wrote:

            What do I have wrong with If ( cdatedegre > 9/1/2012

             

            Thx,

             

            Hi user2259,

             

            You might like to try:

             

            If ( cdatedegre > Date(9; 1; 2012) ...

             

            Regards,

            Ray

            ------------------------------------------------

            R J Cologon, Ph.D.

            FileMaker Certified Developer

            Author, FileMaker Pro 10 Bible

            NightWing Enterprises, Melbourne, Australia

            http://www.nightwingenterprises.com

            ------------------------------------------------

            • 3. Re: Using a date field in a If statement
              RayCologon

              Hi user2259,

               

              As Steve says, the issue is to do with the interpretation of a bunch of numbers - the calc engine doesn't necessarily see them as a date unless you tell it to.

               

              Also, in the interest of simplicity, there's no need to nest the Case( ) and If( ) statements, as you can simply use the date argument as the first line of the Case( ) statement - it will then evaluate before the other arguments:

               

              Case (

              cdatedegre > Date(9; 1; 2012); "Too Late";

              cdegrcode = 1 ; "AA";

              cdegrcode = 2 ; "AS";

              cdegrcode = 3 ; "BA";

              cdegrcode = 4 ; "OTHER BACHELORS";

              cdegrcode = 5 ; "BS";

              cdegrcode = 6 ; "CERTIFICATE";

              cdegrcode = 7 ; "DOC CHIROPRACTIC";

              cdegrcode = 8 ; "DOC OF OESTEOPATHIC SCIENCES";

              cdegrcode = 9 ; "OTHER DOCTORATE";

              cdegrcode = 10 ; "DOC DENTISTRY";

              cdegrcode = 11 ; "DOC VET";

              cdegrcode = 12 ; "JD";

              cdegrcode = 13 ; "MA";

              cdegrcode = 16 ; "MS";

              cdegrcode = 17 ; "NO DEGREE";

              cdegrcode = 18 ; "PHD";

              "No Degree")

               

              Regards,

              Ray

              ------------------------------------------------

              R J Cologon, Ph.D.

              FileMaker Certified Developer

              Author, FileMaker Pro 10 Bible

              NightWing Enterprises, Melbourne, Australia

              http://www.nightwingenterprises.com

              ------------------------------------------------

              • 4. Re: Using a date field in a If statement
                tcwaters

                I had a feeling that it required a formatting for the date, and glad to read why.  Also glad to read that I could simplify my code to eliminate the IF statement.  THX!