6 Replies Latest reply: May 15, 2014 9:51 AM by Grant Perkins RSS

    IF FUNCTION PROBLEM

    TMERRYMAN _

      I am trying to create an IF function. 

      Condition: If my column "amount" is less than 2000.00

      True value:  add a zero to the prefix of my column "number"

      False value:  return column "number" (no zero)

       

      How do I do this?

        • IF FUNCTION PROBLEM
          TMERRYMAN _

          ALSO, I keep getting "operand types" in the message line.  What does that mean?

          • IF FUNCTION PROBLEM
            Grant Perkins

            Originally posted by TMERRYMAN:

            ALSO, I keep getting "operand types" in the message line.  What does that mean? /b[/quote]Usually means you are trying to apply character related changes to a field (or using a field) defined as numeric or vice versa.

             

            Operand count usually means the number of parentheses pairs need to be checked of there are missing parameters related to one or more functions in the formula that make it un-interpretable.

             

            I get all of them all the time!

             

            As for you main problem, are you creating a new calculated field which either containd the value of 'number' field or that value with something added to the prefix depending upon whther the 'amount' filed is above or below a certain value?

             

            If so, where does the prefix come from and is it safe to assume that the resulting field is defined to be a character field? Is the 0 to be added to the beginning or the end of the prefix?

             

            Is 'number' really a number or is it a character field?

             

            Sorry for the questions but hope they help us clarify where the problem lies.

             

            Grant

            • IF FUNCTION PROBLEM
              TMERRYMAN _

              Thanks for the response.  Now let me answer your questions.

               

              I think you understood what I needed.  I want to create a calculated column that if the invoice amount (in the "amount" column) is less than $2000.00, I want a zero added to the front of the invoice number (original invoice number is in the "number" column).  If this condition is false, I want the calculated field to just hold the original invoice number.

               

              The "number" column will be a character.

               

              I hope this helps.

              • IF FUNCTION PROBLEM
                Grant Perkins

                I had written a longer explanation of this but posting trashed it and lost it. Have to dash out but will try to expand on this latter if you want me to.

                 

                Try this.

                [font="courier"]IF(AMOUNT<2000.00=1,"0"+NUMBER,NUMBER)[/font][/quote]HTH.

                 

                Grant

                • IF FUNCTION PROBLEM
                  TMERRYMAN _

                  Thank you Grant...that worked!

                   

                  I was leaving "=1" out.  I guess this means if the condition is true?

                  • IF FUNCTION PROBLEM
                    Grant Perkins

                    Originally posted by TMERRYMAN:

                    Thank you Grant...that worked!

                     

                    I was leaving "=1" out.  I guess this means if the condition is true? /b[/quote]Correct.

                     

                    You could write the formula the other way as well.

                     

                    [font="courier"]IF(AMOUNT<2000.00=0,NUMBER,"0"+NUMBER)[/font][/quote]And there are a few additional ways to write the comparison part.

                     

                    I have to admit it is quote easy to forget sometimes that the formula is looking for a 1 or 0 result from the comparison ...