isnull or " '" ?

[sasa888]

Hi all, I am confused between isnull and = " ".
I created a report which have (Display like they are using the following format)

customer name
Address1
Address2
City, State Zip

Sometimes there will have no Address2, so in order to avoid to see a blank line, I created a function
@function_address2

if not isnull(address2) then
address2
else
City, State Zip


And under to suppress City, State Zip field if Address2 field is null, so i go to format -> Suppress and enter the following

isnull(Address)



The problem is it won't do what I need it to, like to display city, state, zip if address2 is null. Please let me know what I did wrong...........

 

[bdreed35]

Use IdoMillet's technique in this thread thread149-725547 to solve your problem.

~Brian

 

[sasa888]

Thanks for your reply. But if they are coming from an access database, how do I place them into a textbox? Thanks!

 

[lbass]

I think it's simpler just to put each field in its own separate detail section (a to d). Then format each section (format->section) to "Suppress Blank Section."

-LB

 

[bdreed35]

Add a Text Object to the report by clicking the "ab" button on the toolbar.
Add the text object to the section of the report where you want the address to be.
Open the Field Explorer.
Drag Each Field over the Text object until the mouse icon changes to a blinking cursor.
You can add addiitonal text, punctuation, or new lines by hitting the enter key to the text object.
The text object handles resizing each line depending on the size of the data in each database element.

~Brian

 

[lbass]

IsNull does work for string fields if they ARE null, but it is a good practice to also check for empty fields or fields with spaces by using something like:

if isnull({table.field}) or
trim({table.field}) = "" then "Null or empty" else {table.field}

-LB

 

[Madawc]

Null indicates an absence of data, whereas zero or space indicate that there is definitely nothing. Also Crystal formulas stop when they find a null, unless you have tested for it already.

Madawc Williams
East Anglia, Great Britain

 

[duncanmc]

Why not make it easy on yourself.

Use the report expert to create labels. Save it and use this as a sub report, linking the record unique identifiers. By doing this only the address for the unique link will be displayed and all the lines with null values will be factored out and formatted appropriately.

Just did a test of it and it worked a treat.

 

[GMcNamara]

duncanmc,
While your method will only return a label for people who are actually in the dataset, I can't see how it will prevent a blank line from appearing in the case that a field used in the address display is left blank.

 

[cmpgeek]

i was thinking along the same lines of LBs first response... is there a reason giving each line its own detail section and then suppressing blank sections will not work?

... always in a state of ... inspiring to some day experience ...