4 Replies Latest reply on Nov 1, 2014 4:51 AM by NormD

    Find last 2 numbers of a price, in a number field

    NormD

      Title

      Find last 2 numbers of a price, in a number field

      Post

      Did a quick check on the forums for this "simple" find snag but didn't come up with a solution.

      In a text field, I can do a simple find request (no search operators) for any word or combination of letters and it returns exactly what I asked for. ... even tried searching for the letter "q" in a text field of over 150 records. FMP found every one of them no matter where it appeared in the field. Now, a couple of fields over, I want to do the same thing in a number field...  find all the prices that end in  00... ie. 4.00, 7.00, 2.00 etc. (omitting values of 2.99, 3.49, 5.99 etc)

      For example....  in the number field (rounding to two decimals) find:  00
      (...as in the text field mentioned above: find q)

      Although there are hundreds of numbers that end in 00, it finds none and offers up the dialog box to Cancel or Modify the find. Is it something simple I'm missing?

        • 1. Re: Find last 2 numbers of a price, in a number field
          philmodjunk

          Numbers aren't text. Your find criteria is searching for any records where the value of the field is zero.

          But even searching text using the letter q example will not find all records where the text contains "q".

          it would find:

          quiet, q, and quiz

          but not Aqua or Plaq

          You'd need to use *q* to find those records even in a text field.

          You might try this criteria: *.00

          and see if it works for you.

          • 2. Re: Find last 2 numbers of a price, in a number field
            NormDelaney

            Getting a little closer to the solution. Thanks PhilModJunk. Yes, I should have mentioned in my original post that the "find q" text example finds all words beginning with the letter "q".

            As for the "find .00" in the number field, it's still giving me little trouble... but I think it may be the way I have it set up. This number field is a result of a calculation. If the result is, say, 7.98, I can find all the .98's in the field with your above solution. Using the same find structure for "find .00"- still offers up the "Cancel or Modify window. Here's something that may be a clue that I have to check... If I click my mouse in the number field (on the 7.98), all three digits remained visible. If I click on a number that ends in 00 (ie 7.00) the zeros disappear, leaving only the 7. The zeros reappear when I click on another field. Will pursue this further.

            • 3. Re: Find last 2 numbers of a price, in a number field
              philmodjunk

              Yep this is a text type search criteria on a number field and the text, the trailing zeroes simply aren't there. The simplest solution that I can think up is to specify a calculation field with a number result type defined as:

              Int ( yourNumberfieldCalcHere ) = YourNumberFieldCalcHere

              This field will return a 1 (True) only if the two digits after the decimal are zero.

              You can then perform a find for a 1 in this calculation field.

              • 4. Re: Find last 2 numbers of a price, in a number field
                NormD

                Works like a charm PhilModJunk! Thank you again! 

                So glad this forum is here with all the excellent contributors. It helps us less knowledgeable users get a little better at this great program.