Adding empty fields 1

[zupnik]

Hi all,

CR version: 8.5
DB: Access 2002

ex:
I have the following table:

Date Store1 Store2 Store3
1/1 100 200
2/1 200 600
3/1 400 300 500


All fields are number fields
please note that in the DB table Store 3 does NOT have a 0 but a BLANK.

Q: In CR when I add (in a formula) Field{store1}+Field{store2} I get the correct answer of 300 for 1/1, 800 for 2/1, 700 for 3/1.... HOWEVER if my formula looks like this:

Field{store1}+Field{store2}+Field{store3}

the result is a blank.

I suspect that CR cannot add Number values and Blank fields in a DB

Please let me know if there is a way to solve my dilemma...
Unfortunately SQLing Zero's into the blanks of the Store3 field in the DB is not an option...

thanks alot in advance...

 

[Crystalyzer]

How about this in your formula:

NumberVar x:= If ISNULL({store3}) Then 0 ELSE {Store 3};

Field{store1}+Field{store2}+x

I haven't tried it but it might be a start.

 

[zupnik]

Thanks a lot Crystalyzer - worked like a charm