Display blank field as "N.A."?

[onebigscot]

Hello,

I have tried the Keyword search and Osborne's Reference Guide with no luck...

I am working with a SQL database and using Crystal 8.5 to pull data for export into Excel. I have fields which are blank and I would like to autopopulate these fields with "N.A.". Any ideas?

Basically, what I am reporting shows:

Name DOB Hire Date Term Date Term Reason


For those who have not termed, there is no data in that field, nor in the Term Reason field. I would like those to be NA instead of blank.

The Term Reason field reports numbers which are codes for terms (ie: 01 means Resignation) So I have turn it into a Formula Field using If - Then - Else statements to transform the numerical codes into the terms.

IF (Term Reason) = "01" Then "Resignation"

I tried to add:

IF (Term Reason) = "" Then "N.A."

...but that showed nothing (the remaining fields with data were still displaying the correct terminology).

Also - on a side note - is it possible to have two different font sizes in one field?

Thank you for any help you may offer!

 

[lbass]

Replace each field with a formula like:

if isnull({table.field) or
trim({table.field}) = "" then "N.A." else
{table.field}

You can conditionally set the font size for a field. Right click on the formula->format field->font->size->x+2 and enter something like:

if {@yourformula} = "N.A." then 12 else 10

-LB

 

[hilfy]

Try this:

IF {Term Reason} = "01" Then "Resignation"
else IF IsNull({Term Reason}) Then "N.A."

-D

 

[lyanch]

You need to put the isnull test first. If any other comparison is found first, the formula will fail and return "".

So...

If isnull({Term Reason}) then "N.A."
else if {Term Reason} = "01" then etc...

Lisa

 

[lbass]

Oops, noticed I left out a curlicue bracket:

if isnull({table.field}) or
trim({table.field}) = "" then "N.A." else
{table.field}

-LB

 

[onebigscot]

Thanks everyone! I used the...

IF isnull({field.name}) then "N.A." and put it before the rest of the options and it worked.


There is one odd thing though, about ten of the lines (out of 800) don't have the "N.A." displayed even though there is no end date. It works fine on all the other records. I briefly looked at several of the records and cannot find a "smoking gun". I'll poke around further to see why those few records will not show the "N.A."

Thanks Again. : )

 

[lyanch]

They are probably empty string instead of null.. use

else if trim({field name}) = "" then "N.A."

Lisa

 

[onebigscot]

I actually had to use both (maybe that was what you had intended). When I swapped the original code (isnull) with the trim command, the records that were blank now had the "N.A." inserted but the records that previously had "N.A." were now blank.

So now I have both commands in the formula and all that should have the "N.A." are there.

Thanks Lisa!