## "A number is required here" Error

## "A number is required here" Error

(OP)

I created a formula in Crystal Reports 7.0 to subtract a field in our data base which is a string, from the number 99.

Right now I have:

99 - tonumber{OLGL.TRANS_MONTH}

When I run the report it gives me the error: "A Number is Required here".

The data in the field is always numeric, but the field is set up as a string. I need to be able to subtract the field from 99. How do I do this?

Thank you in advance.

Right now I have:

99 - tonumber{OLGL.TRANS_MONTH}

When I run the report it gives me the error: "A Number is Required here".

The data in the field is always numeric, but the field is set up as a string. I need to be able to subtract the field from 99. How do I do this?

Thank you in advance.

## RE: "A number is required here" Error

## RE: "A number is required here" Error

if isnumber({OLGL.TRANS_MONTH}) then

99 - val({OLGL.TRANS_MONTH})

else

0

-k

## RE: "A number is required here" Error

WHEN I TRIED THE SECOND SUGGESTION, IT GAVE ME AN ERROR WHEN I TRIED TO SAVE THE FORMULA. "A NUMBER, CURRENCY, AMOUNT, BOOLEAN OR STRING IS EXPECTED HERE."

ANY OTHER IDEAS?

THANKS.

## RE: "A number is required here" Error

Right click the field and select browse data, Crystal will show you what the data type is from it's perspective.

If it's a string, then the above should be fine, if it's a number then it wouldn't work.

-k

## RE: "A number is required here" Error

if isnumeric({OLGL.TRANS_MONTH}) then

99 - val({OLGL.TRANS_MONTH})

else

0

-LB

## RE: "A number is required here" Error

If NumericText({OLGL.TRANS_MONTH})

then

99 - (ToNumber({OLGL.TRANS_MONTH}))

Peter Shirley

http://www.linkcorp.com

## RE: "A number is required here" Error

=============================================

If NumericText({OLGL.TRANS_MONTH}) then

99 - ToNumber({OLGL.TRANS_MONTH})

else 0

============================================

In my report, I use 3 formulas like the one above to calculate the YR, MO and DAY, then I create a "real date" with this formula:

==============================================

Date ({@YR}, {@MO}, {@DAY})

==============================================

I'm getting a new error; "A month number must be between 1 and 12" Looks like some data in the field is not numeric, but we already knew that! It doesn't make sense since it should alway be numeric, but maybe it's null or got a negative sign or?? What is wrong now?

Thanks in advance.

## RE: "A number is required here" Error

-LB

## RE: "A number is required here" Error

MONTH: A month is normally stored as 01-12, but in my database it is stored as 98-87. Example: 01 is stored as 98. In this example, to get the real month I subtract 98 from 99.

DAY: A day is stored the same way, but dates can be 01-31 and so are represented as 98-68.

YEAR: A year is a little different because of the century, but the calculation is similar as is the code. However, the year seems to be working ok. No errors yet.

1999 = 00

2000 = 99

2001 = 98

2002 = 97

2003 = 96

2004 = 95

etc etc

## RE: "A number is required here" Error

Try:

if not isnull({OLGL.TRANS_MONTH}) then

99 - tonumber({OLGL.TRANS_MONTH})

else

0;

## RE: "A number is required here" Error

Is there any way around this? Another method?

## RE: "A number is required here" Error

ie)

if {@YR}<> 0 and {@MO} <> 0 and {@DAY} <> 0

then Date ({@YR}, {@MO}, {@DAY})

Lisa

## RE: "A number is required here" Error

Peter Shirley

http://www.linkcorp.com

## RE: "A number is required here" Error

One of the things all this tells you is that your data isn't as bulletproof as you thought (for example it isn't always numeric text, you have null values, and apparently since you still get the "a month must be between 1 and 12" error, your database Month field must either have values that are outside your expected 98-87 range, or if you are defaulting to a zero value at any point and then are subtracting from 99 then you will get a numeric value that is more than 12 which is why you get the error message). It might be a good idea to do some simple SQL queries on that particular field so you can see what actual values,nulls, or other data is showing up for that field and you will be able to appropriately test for any and all potential "problem" data values.

## RE: "A number is required here" Error

## RE: "A number is required here" Error

Peter Shirley

http://www.linkcorp.com

## RE: "A number is required here" Error

Thanks to one and all who replied to this thread. You're all wonderful! :)

## RE: "A number is required here" Error

Peter Shirley

http://www.linkcorp.com