Concatenating blank fields

[peierls]

I have a formula which is just the concatenation of two text fields. For any record for which each field contains at least one non blank character, the formula produces the correct result in the report. If one of the fields is empty, or just contains blanks, then the result of the formula is just blank.<br><br>I have tried with several formulas with more than two fields, and using different fields, and the result is always the same, i.e. a blank component in a concatenation seems to wipe oit all the non-blank ones. <br><br>Any ideas?<br>&nbsp;

 

[MalcolmW]

What you are describing as &quot;blank&quot; is in database terminology &quot;null&quot;.&nbsp;&nbsp;The closest english equivalent is &quot;unknown&quot;.<br>For example, three people with their hair color.<br>Name&nbsp;&nbsp;&nbsp;Color<br>-----&nbsp;&nbsp;-------<br>Tom&nbsp;&nbsp;&nbsp;&nbsp;'Black'<br>Dick&nbsp;&nbsp;&nbsp;'Blond'<br>Harry&nbsp;&nbsp;&nbsp;NULL<br><br>Harry, although his name suggests otherwise, is bald.&nbsp;&nbsp;Because people can be bald, NULL values are allowed in the Color field. <br>If you want to create a report on company hair colors, you will need to deal with this.<br>If IsNull({Table.Color}) then<br>&nbsp;&nbsp;{Table.Name} + ' is bald as an egg!'<br>Else<br>&nbsp;&nbsp;{Table.Name) + ' has ' + {Table.Color} + ' hair.'<br>//end<br>If you don't test for nulls, you would be concatenating an unknown value into a string.&nbsp;&nbsp;Therefore the resulting string would be unknown (null) as well.<br>This similar logic applies to Nulls for numbers and dates.&nbsp;&nbsp;<br> <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>

 

[peierls]

Thanks for the suggestion. I had rejected that explanation because the behavior persisted when I replaced the empty field with a series of blanks. But I have just confirmed the fact that such a field is automatically truncated to &lt;null&gt; and so your explanation makes sense.&nbsp;&nbsp;I guess I am more accustomed to languages which distinguish zero-length strings from &lt;null&gt;s!

 

[MalcolmW]

Sounds like I gave you more detail on nulls than you needed - sorry.<br>Crystal is not without faults, but this one probably depends on what sort of database and method of access are you using.&nbsp;&nbsp;<br>I tried it with SQL Server 7, and concatenation with empty strings works fine, concatenation with null strings produces null - standard SQL stuff.&nbsp;&nbsp;That was using direct access, not ODBC.<br>Different databases/versions operate under different rules - SQL Server 6.5 stores empty strings as one blank character, so its empty string has a length of 1.&nbsp;&nbsp;God knows what Access does across its various versions.<br>I sympathise with you on how common poor implementation of nulls is, especially with ODBC drivers.&nbsp;&nbsp;I'll spare you my further ranting on this topic. <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>