Help with Crystal reading fields with Zeros or nothing in... 1

[ukleaf]

I know it's possible but for some reason I can't get this to work!

basically I have several fields on our system, allot of them are set to zero, Crystal wont include these in the addition formula... if I change the formula to add those fields with an amount in it works fine, if I then change it back to add everything up it doesn't display a total. Now this is the strange thing, if I then put in a amount in each of the Zero fields, save it and then remove the amounts putting the Zero back it works fine.

It's like Crystal will only add if the field has previously been populated with an amount, whether it's there now or not...

Can anyone help with this please,

Many thanks

 

[Reebo99]

It's because of Null values.

A field which has never had a value will generally default to NULL. If you type in 0, then the field value becomes 0. If you then delete the value, the default will generally become 0.

In order to solve this, create a formula field :

If IsNull({field)) then 0 else {field}

Change the items in bold with you real field. Then do all you calculations on this field.

Hope this helps...

Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.

 

[ukleaf]

Thanks very much for that.

there are no errors when I save the formula, the table has a Left outer join but it's still showing nothing unless I put in a amount then take it out agian. here is a snippet of my code in the formula.

If IsNull({_syusr_usr_wrk_dtls.azurtime}) then 0 else {_syusr_usr_wrk_dtls.azurtime};
If IsNull({_syusr_usr_wrk_dtls.aatstime}) then 0 else {_syusr_usr_wrk_dtls.aatstime};
\\..
{_syusr_usr_wrk_dtls.azurtime}+{_syusr_usr_wrk_dtls.aatstime}

Thanks allot for your help.

Dean

 

[Reebo99]

Change the formula to :

Local NumberVar tmpValue1 := If IsNull({_syusr_usr_wrk_dtls.azurtime}) then 0 else {_syusr_usr_wrk_dtls.azurtime};
Local NumberVar tmpValue2 := If IsNull({_syusr_usr_wrk_dtls.aatstime}) then 0 else {_syusr_usr_wrk_dtls.aatstime};

tmpValue1 + tmpValue2;

Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.

 

[ukleaf]

Yep it dawned on me after I posted the first reply that Crystal is unable to write back to a system so I would have to use variables... here is what I did.

GLOBAL NUMBERVAR ZUR;
GLOBAL NUMBERVAR ATS;
\\.......
If IsNull({_syusr_usr_wrk_dtls.azurtime}) then ZUR := 0 else ZUR := {_syusr_usr_wrk_dtls.azurtime};
If IsNull({_syusr_usr_wrk_dtls.aatstime}) then AA := 0 else AA := {_syusr_usr_wrk_dtls.aatstime};
\\.......
ZUR+ATS...

It's working like a treat now, again many thanks for your help.

Dean