Hi everyone, been working on this to long and need some help.

CR 11/sql2005

Current Output
Amount 1        Amount 2
1.00            2.00
                0.00

Desired Output as follows:
Amount 1        Amount 2
1.00            2.00
-               0.00

Desire is to show a hyphen where blank data is returned.

the sql table will hold zero's if, at runtime, there is suppose to be data but nothing has been entered. The table will also show blanks where data is not to be entered.  I can convert the blanks to zero with the report options but that is not what I need. Isnull does not detect these blanks as Null.

Blank = DASH
Zero = Zero

Thanks,
Gary



 

If amount is a number field, then I think a "blank" can only appear for a null.

What do you see if you add a formula like instead of amount1?

if isnull(amount1) then
"Blank" else
amount1

-LB
 

Yes, you are correct, that will create the dash. now the problem is on the ELSE portion it says a string is required here so I did totext({amount1}) and it seems to work. Do you see any problems with that solution?

if isnull({amount1}) then
"-" else totext({amount1})

Sorry, yes, you'd have to use totext(). The problem is that you won't be able to use the results for calculation then. Another option would be to use the formula in the display string formatting area, but leave the values as numbers on the report. If you want to control the number of decimals, you can use:

totext(amount1,0,"") //0 for no decimals; "" for no comma, etc.

-LB