Select Statement Not Recognizing a Zero 1

[Rob7]

Hi All,

This is Crystal XI sp2 running on a Progress Database.

I am trying to select on a numer field and get records that are a zero. So, in the select expert, I choose the field (QtyShipped) then the condition (is equal to) and enter zero in the field. When I click OK, I get an error indicating that "A number, currency amount, boolean, date, time, date-time or string is exected here." Isn't zero still a number? Is this an issue with Crystal? I have tried using the ToNumber fuction on it to make sure the field is numeric but nothing so far is working. Any help is greatly appreciated.

Thanks,

Rob

 

[Durango122]

hi
check the report option and check convert to null
so null int is 0
string null is ""


Durango122
if it moves and should not used Duck Tape
if does not move and should used WD-40

 

[Rob7]

I did that and I am still getting the error. I also created a formula field with:
If IsNull ({JobProd.QtyShipped}) Then 0
Else
{JobProd.QtyShipped}

Neither of these worked.

 

[lbass]

Are you using multiple tables? If so, please identify how they are joined.

-LB

 

[Rob7]

LB,
I was but I removed all but the one table. I also looked at the data in another reporting tool and there are in fact zeros in the field. And another interesting thing is that when I browse the data in the select expert, there is no zero prior to the 1.00 in the list of browsed values. It is a dash (-). I would think I have a corrupted database but I have a number of databases and all of them are showing this dash. These are not different types of databases, just different datasets of the same type of database.

 

[elsenorjose]

I'm sure you'll get a few replies to this but '-' is not the same as zero.

When you say 'another reporting tool' what are you using to view the data? It seems odd that an application would show a '-' as a 0.

If the value really is a '-', just change your formula:

If ({JobProd.QtyShipped}) = '-' Then 0
Else
{JobProd.QtyShipped}

 

[Rob7]

The other reporting tool is a query tool thats built into the application the database works with. And I did try that formula but because this is a numeric field, Crystal tells me that the "-" is not acceptable. It has to be a number. I think I have something wrong with my crystal installation. Maybe missing a .dll or something. I have talked to others using this same database and the same version of Crystal and they are seing a zero in the browsed data list, not the (-).

 

[lbass]

elsenorjose's formula would not return that error unless {JobProd.QtyShipped} was NOT a number, since the relevant comparison is between 0 and the field. Can you verify that your quantity shipped field is in fact a number? Look at the datatype shown in the tooltip when your mouse hovers over the field.

Also, I think you should look at the way the field is displayed in the detail section without any selections. Note that "-" is a valid display format for a field value = zero. If you go to format->number->customize->display zero values, one of the options is "-". This would not affect your selection formula, however, which would still be set up as:

{JobProd.QtyShipped} = 0

...as long as the quantity is in fact a number, but your results suggest that it is not a number.

-LB

 

[Rob7]

Just a short note to end this. I spoke with Crystal support and they did not know anything about this problem and suggested I uninstall and reinstall Crystal. This did work and the dash is gone from the browsed data and I can use the select expert to select for Zero.

Thanks for looking everybody.

Rob