Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 deal | Length of Sales Deal Field | Count of Sales Deal |
z | 1 | 221 |
z | 10 | 221 |
Example with actual data that did not have an empty field
Sales deal | Length of Sales Deal Field | Count of Sales Deal |
0000023833 | 1 | 4 |
0000023833 | 10 | 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
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
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