select blank records if Convert NULL field to default is turned on

[dbradley]

Hi, I have several reports that require I have the convert database null values to default option turned on. With this option turned on, how can I write a formual to select blank values?

The field on my report displays as "0.00" & it is a number data type, but in the select expert this formula does not work (it returns zero records)

{table1.sp_id} = 0.00

Does anyone know how to select these blank values?

-----------
DB

 

[synapsevampire]

I would test using:

isnull({table1.sp_id})
or
{table1.sp_id} = 0.00

I suspect that your record selection formula is being passed to the database, so the default values hasn't taken effect yet.

-k

 

[dbradley]

Hi, neither of these work -- the isnull returns false (because of the convert database nulls to default) and if I ask for {table1.sp_id} = 0.00

zero records are returned.

Any other ideas?
----
DB

 

[synapsevampire]

I assume that you tried them in combination as opposed to NEITHER of these work?

Anyway, another solution might be to create a SQL Expression of iif(table.field,null,0,table.field) or some such (you really should post technical information such as your version of Crystal and the databasae being used since your asking about both things, you'll need to use yyour databases syntax), this should be offloaded to the server and return the appropriate results. Then you can key off the SQL Expression field in your record selection.

-k

 

[lbass]

You could try:

round({table1.sp_id},2) = 0

Better yet, uncheck the convert null values to default and use SV's original formula.

-LB

 

[dbradley]

Hi, the best solution I came up with is first writting this formula (I called it convert)

CDbl ({table1.sp_id})

Then using the select expert to select {@convert} = 0.00

This seems to work for me.

-------------
DB