Addresses and Combining Fields

[bhansen]

I am trying to print out my clients address on a single line, but the problem is that if any of my fields contain a null the formula is left blank. Also my numeric values converted to string have a comma in them (9,201 instead of 9201).

My data consists of 5 fields as such;
1 Apartment Number (Numeric) - 101
2 Apartment Number (String) - B
3 Building Number (Numeric) - 9201
4 Street Name (String) - Ocean
5 Street Indicator (String) - Ave

I would like to see;
101 - B - 9201 Ocean Ave or
9201 Ocean Ave (if fields are null).

My current formula is as such
Totext ({1},0)+" - "+{2}+" - "+Totext({3},0}+" "+{4}+" "+{5}

Does anyone have a way to fix the null values and my comma problem

 

[dgillz]

totext({3},0,"&quot will get rid of the thousands separator.

The just test for nulls the entire length of your formula:

If Isnull({1}) then "" else Totext ({1},0,"&quot+if Isnull({2}) then "" else " - "+{2}+if isnull({3}) then "" else " - "+Totext({3},0,""}.......

You get the idea? Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[bhansen]

This did not seem to work the formula stopped after hitting the first null.

Thanks for your help though because it did turn me in the right direction.

 

[Ngolem]

look at threads in this forum....do a search...there are many of them that "address" the address problem. Jim Broadbent

 

[kenhamady]

You need to put each phrase in parentheses, or the the first IF will not look past the first THEN:

(If Isnull({1}) then "" else Totext ({1},0,"&quot ) +
(if Isnull({2}) then "" else " - "+ {2} ) +
(if isnull({3}) then "" else " - "+ Totext({3},0,""} ) + ... Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com