Displaying a value where there is a nothing (blank)?

[VBAnewguy]

Ok, hard to put into words, please have patience.

Background:
I have a report that displays in the following manner:

(ignore underscores, entered to help preserve formatting)

Salesperson______Sales_____Quota
Bob______________$500______$900
John___________$1,200_____$1000

Salesperson is pulled directly from the salesperson table, Sales and Quota are being displayed from Formulas (simple Select Case formula based on a parameter from the user), based on the Month desired.

The Salesperson data table is linked via an office number and salesperson number to a Sales Table and a Quota table. I had to play with the join to get the data from both tables to be returned.

The Sales table has a one record for each salesperson that actually HAS sales (if John had no sales, there would be no record).

The Quota table has one record for each salesperson (even if they have no quota). If John had no quota, there would be 0 (zero) in the quota file for John.

Problem:
When Crystal pulls the data, it looks like this:

Salesperson______Sales_____Quota
Bob______________$500______$900
John____________$1200_____$1000
Suzy____________$1500________$0
Sam____________ _____$500
Rick_____________$500________$0
Frank__________ _______$0
Jim______________$500______$900
Pete____________$1250________$0

Notice that even though Suzy and Pete have a ZERO quota, there is a zero returned (reflective of the record in the quota table). This is good,

Notice that Sam and Frank have a BLANK in the Sales column, reflective of there not being a record available. This is bad,

Is there anyway to get something to appear instead of BLANKS?

In my application, if someone has a quota, but no sales, the sales should be ZERO.

I have tried altering my Sales and Quota formulas with Default values, but, I am assuming that the Select Case will only work once the query has returned data (and not if there is no data there.

Any advice would be greatly appreciated, thanks in advance.

Jason


v10, propriatary record based DB via ODBC(DAO) link

 

[kskid]

Try this click on file->Report Options and check convert Null Field to default.

If that doesn't work, then create a formula on the sales

//@Sales
if isnull({sales}) then 0
else
{sales}

On your report canvas replace the field {Sales} with the formula {@Sales}

Cheers,
-LW

 

[synapsevampire]

Another means is to select File->Options and find the convert null to default value. Then you should get zeroes as well.

-k

 

[VBAnewguy]

Thanks guys,

The convert nulls to default did not work unfortunately, i did try....

The second option DID work, I had tried putting an if then into the original sales formula (which calculates which field to pull from the sales table based on the month the user selects),

Creating two seperate formulas worked,

Thanks wichitakid for the solution.

 

[synapsevampire]

I guess because there aren't any rows being returned it's not converting anything, although it might have been that you needed to do this under file->report options, under options it does so globally, but not for previous or currently opened reports.

-k

 

[mpvel]

synapsevampire,

In our case, I am already using the formula as VBAnewguy did to convert the blanks to zero's. Now the users want in a different way. If the record field value is zero then display zero or if the record field value is null then display "-". If I changed the formula to display the number using Cstr function, then I was having problem to format the number field because it turns into string field on the report. I have running totals based on these fields.

So is there anyway to keep the field numeric and display
"-" for null values.

 

[lbass]

You could use a display formula for display purposes only:

if isnull({table.number}) then "-" else
totext({table.number, 2,"") //here is where you can format the display:
//the number is decimals, the "" means no comma separator

For your calculations, use:

if isnull({table.number}) then 0 else {table.number}

-LB