Form Report Null Values

[TheMagikWand]

Hey Guys/Gals,
I have created a crystal reports v8.5 form letter that displays a patients name, insurance info and a small blub about the procedure theyre having. it looks like this:

[MediCare]
[100 W. 30th Street]
[New York], [NY] [10001]

[Ms.] [Susan James],
[Info of the procedure, blah blah blah.]

Everything I put in brackets is a separate field from my database, but recently ive been noticing that some of the insurance companies in our database have missing addresses.
What Im asking in a roundabout way is When I have a null value for an address is there any formula i can have that will not only not show the data, but have the lines the data was occupying , and the comma separating the city,state to not show up as well. It just looks quite odd to have about 6 lines between 2 pieces of infomation

 

[DallasAggie]

Place a text box in your Details Section, then:

--- Place your [MediCare] filed inside of Text Box.
--- Next line add your Address 1 Field
--- Next line add your Address 2 Field (if any)
--- Next line add your @CityStateZip Formula
--- Go to properties of Text Box and Select "Supress Embedded Field Blank Lines"

That should do it.

@CityStateZip Formula : Note, this is an example only -- this is what I am using if the City Field is null/blank -- you should modify if you want to do something else with State/Zip Code fields....Good Luck!

IF ISNULL ({contacts.city}) =TRUE OR {contacts.city}= "" THEN
{contacts.fk_state}& " " & {@zips}
ELSE
{contacts.city} & ", " & {contacts.fk_state}& " " & {@zips}

 

[rosemaryl]

Dear MagikWand,

Another option, because I don't like to use fields in text boxes as it does cause a performance hit, is to create one formula that populates the information and shows it the way you want:

Stringvar Company;
Stringvar Addy;
StringVar City;
Stringvar ST;
Stringvar Zip;

Company :=  if isnull({Company.Company Name}) or {Company.Company Name} = ''
            then '' 
            else {Company.Company Name} + chr(13);
Addy :=     if isnull({Company.Address}) or {Company.Address} = ''
            then '' 
            else {Company.Address} + chr(13);

City :=     If isnull({Company.City}) or {Company.City} = ''
            then ''
            else {Company.City} +',';

ST :=       If isnull({Company.State}) or {Company.State} = ''
            then ''
            else {Company.State} + '  ';

Zip :=      If isnull({Company.Zip}) or {Company.Zip} = '' or {Company.Zip} = '_____-____' //because of specif db I use
            then ''
            else {Company.Zip};

Company + Addy + City + ST + Zip

Hope that is useful,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[TheMagikWand]

thanks alot to both of you for answering
since the majority of my form letters do have fields inside the text boxes, i really am too lazy to redo the entire structure of it
either way, thanks again for your help