7 Replies Latest reply: May 15, 2014 9:53 AM by Carol _ RSS

    New user in need of help

    ZJSoule _

      Hello Monarch forum!

       

      I am a new Monarch user, and I'm very impressed with the software so far.  I am using Monarch Pro V8.

       

      Today I have encountered a problem that has me beating my head against the wall.

       

      I have a field containing SSN's and I need only the last 6 digits of the numbers.  The report was extracted from a database so its a .dbf file that I am working with.  The tricky part is, the database did not extract any zeros that were in front of the SSN

       

      example:

      normal SSN: 123456789

      SSN with zeros in front:  3456789

       

      They are all right justified in the table.  I only need the last six, so not having the zeroes in front should not theoretically present a problem.  However, no matter what I do, I get an operand type error whenever I try to make a new calculated field that just takes the last 6 digits.

       

      I have tried the functions in every way I can think of, to no avail.  Any assistance would be greatly appreciated.

       

      Thanks,

       

      Zach

        • New user in need of help
          Grant Perkins

          Hi Zach and welcome to the forum.

           

          Operand error usually implies the function in use is not appropriate for the field type.

           

          Looks like you have NUMERIC fields coming in from your dbf file and I'm guessing you are using the RIGHT() function to grab the last 6 digits.

           

          Right() work on Alpha fields. So if you first convert the Numeric to Alpha using the STR() function you should get what you want assuming you are happy with an Alpha field as the result.

           

          [font="courier"] STR(Right(SSN_Field,6)) /font[/quote]If you need Numeric output for some reason just add a conversion to the formula to take it back to numeric.

           

          [font="courier"]VAL(STR(Right(SSN_Field,6)))  /font[/quote]One of those should do for you unless I have misunderstood something or made a typo!

           

           

          HTH.

           

           

          Grant

           

           

                  •    

           

          See below for the correct versions of these formulae as provided by Data Kruncher. 

           

          *****

           

          [size="1"][ October 25, 2007, 11:57 AM: Message edited by: Grant Perkins ][/size]

          • New user in need of help
            ZJSoule _

            Grant,

             

            Thanks for the quick reply!

             

            I tried both of those formulas, and got the same error.  Its truly baffling.  I even converted the .dbf to a .txt and tried it there in case perhaps there was some issue with the database, but I still got the same errors.

             

            If you have any more thoughts on the subject, I would appreciate your insight.

             

            Zach

            • New user in need of help
              Grant Perkins

              Originally posted by ZJSoule:

              Grant,

               

              I tried both of those formulas, and got the same error.  Its truly baffling.  I even converted the .dbf to a .txt and tried it there in case perhaps there was some issue with the database, but I still got the same errors.

               

              If you have any more thoughts on the subject, I would appreciate your insight.

               

              Zach /b[/quote]Now that does sound strange, especially if you have also have the same problem with a .txt file. ( I assume by converted you mean a sort of export from dbf not just a rename. Have to check these things ...)

               

              What is the field type in the table?

               

              What is the field type you have selected for the calculated field?

               

               

              Grant

              • New user in need of help
                Data Kruncher

                Gents,

                 

                You're close. Assuming that field type of the calculated field is numerics, what you're after is:

                 

                [font="courier"] Val(Right(Str(SSN_Field),6)) /font[/quote]Convert the SSN to a string, take the rightmost six characters, then convert it back into a numeric value.

                 

                Kruncher

                • New user in need of help
                  Grant Perkins

                  Originally posted by Data Kruncher:

                  Gents,

                   

                  You're close. Assuming that field type of the calculated field is numerics, what you're after is:

                   

                  [font="courier"] Val(Right(Str(SSN_Field),6)) /font[/quote]Convert the SSN to a string, take the rightmost six characters, then convert it back into a numeric value.

                   

                  Kruncher /b[/quote]AH!

                   

                  Good spot Kruncher. Never was any good at proofreading my own output ... hence the caveat in the original post!   [img]redface.gif[/img] 

                   

                   

                  Grant

                  • New user in need of help
                    ZJSoule _

                    Guys,

                     

                    It worked like a charm!  Thanks for the help!

                     

                    Zach

                    • New user in need of help
                      Carol _

                      Thanks to all who help those of us in need on this forum.  It is, by far, the best forum I have ever used.  I almost always find my answer.

                       

                      A special thanks to Grant Perkins and Data Kruncher for their reply to "New User in need of help".  I, too,  have been bedeviled by this "operand error" for years.  In Excel I manage to work my way out.  But in Monarch I have not been able to until this answer to this question.  I am going to laminate this answer and post it in my office.

                       

                      Thanks again to Grant Perkins and Data Kruncher.