5 Replies Latest reply: May 15, 2014 10:05 AM by Olly Bond RSS

    Calculated Fields

    ruby _

      I have three columns of info. and I want to perform the following:  In column 3, If column 1 starts with a 9 then display back column 2 otherwise put a zero (in column 3).  Any ideas on how to go about doing this without getting an error?

       

      Thanks!!!

        • Calculated Fields
          Data Kruncher

          First, define Field3 as a Numeric field, and Field2 must be a Numeric field as well.

           

          If Field1 is also Numeric, then this will work:

          if(Left(Trim(Str(Field1)),1)="9",Field2,0)[/CODE]

           

          If Field1 is a Character field, then it's a bit simpler:

          if(Left(Field1,1)="9",Field2,0)[/CODE]

           

          HTH,

          Kruncher

          • Calculated Fields
            rlanto _

            Pat #                  Admit Dt

            5624773952M     7/20/2007

             

            I installed the software lastweek and in discovery phase at the moment.  I'm creating an Access table from a text file using Monarch and I need to merge the two fields to look like this 5624773952M7/20/2007 (text field) for use in lookup/joins later.

             

            Could anyone show me how?

             

            Thanks:)

              • Calculated Fields
                Grant Perkins

                Pat #     Admit Dt

                5624773952M    7/20/2007

                 

                I installed the software lastweek and in discovery phase at the moment. I'm creating an Access table from a text file using Monarch and I need to merge the two fields to look like this 5624773952M7/20/2007 (text field) for use in lookup/joins later.

                 

                Could anyone show me how?

                 

                Thanks:)[/quote]

                 

                Hi rlanto and welcome to the forum.

                 

                The basic concatenate feature is, in a new calculated field, simply Feild1+Field2 for CHARACTER fields.

                 

                Assuming you Pat # to be a character field you can use it as it is unless the size varies in terms of number of characters. If so you may (or may not) need to consider using the TRIM function on the field as part of the formula. Or one of the variants of the TRIM function to suit - RTRIM for example. TRIMs will remove spaces where required.

                 

                If your DATE is a character field then you can also use that as it is, possibly with TRIM once again.

                 

                If it is a DATE FIELD you will need to convert it to a character field. The examples in the Help for the DtoC function should be useful, again perhaps with the TRIM function if required.

                 

                To some extent how you define it is dependent on how much controol you have over the data to be joined to later. Obviously they need to match accurately for the lookup to work so a little planning is useful. That said the whole formula can be changed in moments so getting it not quite right is no big deal so long as you know where it is not quite right!

                 

                HTH.

                 

                 

                Grant

                  • Calculated Fields
                    Olly Bond

                    Hello rlanto,

                     

                    Just one word of warning - if you perform a function on fields where one of them is null then you will get a null result. e.g. "olly"+(null) is not "olly", it's (null). If you're building fields as calculations, you can use a formula like:

                     

                    if(isnull()=1;"";[fieldname])

                     

                    to fix that risk - it replaces null values with the empty string. If you are mining your data from a report source, then you can select in Options > Input to replace null values with default values.

                     

                    Good luck!

                     

                    Olly