Remove bullets with Null Field 1

[shellirene]

I am using Crystal Reports 2013 and Sql Server 2012 and am very new with both.
I am trying to build an address that looks like this

AddLine1 • AddLine2
City • State • Zip

With the bullets removed where there are null values.
This doesn't seem to work at all since the bullets display no matter which field is null.

Can anyone help me figure out what I have done wrong?

Stringvar Uadd2;
Stringvar Uaddcity;
Stringvar Uaddstate;
Stringvar UaddZip;
Stringvar bullet12;
Stringvar bulletCS;
Stringvar bulletSZ;


Uadd1 := If not isnull ({table.FAMUSAddLine1}) then ({table.FAMUSAddLine1}) else "";

Uadd2 := If not isnull ({table.FAMUSAddLine2}) then ({table.FAMUSAddLine2}) else "";

Uaddcity := If not isnull ({table.FAMUSCity}) then ({table.FAMUSCity}) else "";

Uaddstate := If not isnull ({table.FAMUSState}) then ({table.FAMUSState}) else "";

Uaddzip := If not isnull ({table.FAMUSZip}) then ({table.FAMUSZip}) else "";

bullet12 := If not isnull ({table.FAMUSAddLine1}) and not isnull ({table.FAMUSAddLine2}) then " • " else "";

bulletCS := If not isnull ({table.FAMUSCity}) and not isnull ({table.FAMUSState}) then " • " else "";

bulletSZ := If not isnull ({table.FAMUSState}) and not isnull ({table.FAMUSZip}) then " • " else "";

Uadd1 + bullet12 + Uadd2 + ChrW(13)
+ Uaddcity + bulletCS + Uaddstate + bulletSZ + UaddZip;

Thanks Michele

 

[pmax9999]

Your code actually did work when I tested it. I think you will find the reason it is not working is that the fields are actually empty strings (or perhaps even one or more spaces) rather than null values.

Given your approach replaces null fields with empty strings, you could achieve what you want by testing the variables for empty strings rather than the fields for null values in your bullet variables. The resultant formula would then look like this:

Stringvar Uadd1;
Stringvar Uadd2;
Stringvar Uaddcity;
Stringvar Uaddstate;
Stringvar UaddZip;
Stringvar bullet12;
Stringvar bulletCS;
Stringvar bulletSZ;


Uadd1       :=  If not isnull ({table.FAMUSAddLine1}) then ({table.FAMUSAddLine1}) else "";
Uadd2       :=  If not isnull ({table.FAMUSAddLine2}) then ({table.FAMUSAddLine2}) else "";
Uaddcity    :=  If not isnull ({table.FAMUSCity}) then ({table.FAMUSCity}) else "";
Uaddstate   :=  If not isnull ({table.FAMUSState}) then ({table.FAMUSState}) else "";
Uaddzip     :=  If not isnull ({table.FAMUSZip}) then ({table.FAMUSZip}) else "";
bullet12    :=  If      Trim(Uadd1)     <> "" and 
                        Trim(Uadd2)     <> "" 
                Then    " • " 
                Else    "";
bulletCS    :=  If      Trim(Uaddcity)  <> "" and 
                        Trim(Uaddstate) <> ""
                Then    " • " 
                Else    "";
bulletSZ    :=  If      Trim(Uaddstate) <> "" and 
                        Trim(UaddZip)   <> ""
                Then    " • " 
                Else    "";

Uadd1 + bullet12 + Uadd2 + ChrW(13) + Uaddcity + bulletCS + Uaddstate + bulletSZ + UaddZip;

Hope this helps

Cheers
Pete

 

[shellirene]

Thanks Pete!! That worked great and was just what I needed.