2 Replies Latest reply: Jan 23, 2012 12:47 AM by Rob Wunderlich RSS

    Field Length Issue - Anyone ever see this before?????

      Has anyone ever seen this, I have a Char field that has a length of 10 char.  In the data, the field would appear empty and I wanted to add a 'z' to it if it was empty. I had issues with trying to trim this field, using isnull, and then I had to finally do a purge char which got rid of the majority of them; Now I have this,

       

      Example with the Z added:

      Sales   dealLength of Sales   Deal FieldCount of Sales   Deal
      z1221
      z10

      221

      Example with actual data that did not have an empty field

      Sales   dealLength of Sales   Deal FieldCount of Sales   Deal
      000002383314
      000002383310

      18

      How could this sales deal field have a length of 1 and a length of 10 with the same value in this field?  I want to be able to delete all records where the sales deal = z and doing an inner join where it's <> 'z' is not working.  It did earlier but I am still left with 221 of them.  ANy thoughts?Thanks, Sean

        • Field Length Issue - Anyone ever see this before?????

          Have you tried with

           

          isnull(field) or field=null() or len(trim(field)) = 0 (or trim(field) = '')  

           

          ?

           

           

          of course 3 and 4 are the same. Remember that null() and isnull(field) is not the same.

           

           

          The actual lenght of the field is not working properly, but maybe you need to post the script and expression.

           

          Regards

          • Re: Field Length Issue - Anyone ever see this before?????
            Rob Wunderlich

            A database char field can contain blanks which is not the same as database null. Additionally, there have been issues with consistency of IsNull() on the 64bit platform. There are also differences between blank trimming on database reads and file reads (controlled by Verbatim= variable). I've found the most reliable way to detect "empty" fields for all cases is

             

            len(trim(field))=0

             

            Although I will admit that your "z" example looks strange. I  debug these cases with an listbox expression of

             

            ='>' & field & '<'

             

            What expression are you using for your "Length of Sales Deal Field" field? Is that a calculated dimension or an expression?

             

            -Rob