"A number is required here" Error 1

[eileenr27]

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.

 

[cjlarue]

Try 99 - (tonumber({OLGL.TRANS_MONTH}))

 

[synapsevampire]

Just in case, try:

if isnumber({OLGL.TRANS_MONTH}) then
99 - val({OLGL.TRANS_MONTH})
else
0

-k

 

[eileenr27]

WHEN I TRIED THE FIRST SUGGESTION, IT GAVE ME AN ERROR WHEN I RAN THE REPORT WHICH SAID "STRING IS NON-NUMERIC".

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.

 

[synapsevampire]

Try posting where and what you tried exactly by pasting it in here.

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

 

[lbass]

I think SV meant:

if isnumeric({OLGL.TRANS_MONTH}) then
99 - val({OLGL.TRANS_MONTH})
else
0

-LB

 

[crystalreporting]

Or..

If NumericText({OLGL.TRANS_MONTH})
then
99 - (ToNumber({OLGL.TRANS_MONTH}))

Peter Shirley

http://www.linkcorp.com

 

[eileenr27]

OK. I got past the number required here error with this formula:
=============================================
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.

 

[lbass]

The message is saying that your middle value, {@Mo}, is out of range. If {OLGL.TRANS_MONTH} has results between 01 and 12, then your formula will return a number between 87 and 98, noe of would qualify as a month number. Perhaps you could explain what you are trying to do by subtracting the month number from 99.

-LB

 

[eileenr27]

THIS WHY I AM SUBTRACTING THE MONTH FIELD FROM 99: The database I'm working with contains dates stored as separate fields. Further, a code is used to represent the day, month or year. After the calculations are done I create a "real date" from them.

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

 

[pbateman]

I read your original question and think your problem maybe due to NULL values in {OLGL.TRANS_MONTH}. That would give you the error you're getting.

Try:

if not isnull({OLGL.TRANS_MONTH}) then
99 - tonumber({OLGL.TRANS_MONTH})
else
0;

 

[eileenr27]

I agree with you. I think the field may be null, but your formula also gives me the error, about "a month must be between 1 and 12" because in those instances where the condition is present we are making it 0.

Is there any way around this? Another method?

 

[lyanch]

Change your date calculation to ignore when the date is null.

ie)

if {@YR}<> 0 and {@MO} <> 0 and {@DAY} <> 0
then Date ({@YR}, {@MO}, {@DAY})


Lisa

 

[crystalreporting]

I think if it were me, and the potential exists for the database to not have 'correct' values in it (nulls etc.) than I'd make the formula account for every possibility e.g. build in an if statement on the day, month and year to always ensure a correct date is returned. This is good practice with any formula, and may save your successor, or someone else who maintains the report, headaches in the future.

Peter Shirley

http://www.linkcorp.com

 

[mrudolph]

In addition to the test for NULL values and the IsNumericText test on your database field for month, you are going to need to test your intermediate @Month formula to make sure it is a value between 01 and 12 before you use the @Month in the Date function.

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.

 

[eileenr27]

OK, as you suggested, I did a query to look at the data. What I think is causing the problem is because the database contains an end balance for each individual ledger. When it says "end balance" in the description field there is nothing displayed for the month or day.

 

[crystalreporting]

So you can omit those records from your report right? I'd do that if possible, but still build your formula to handle possible exceptions..

Peter Shirley

http://www.linkcorp.com

 

[eileenr27]

Yes. I unselected those records and was finally able to run the report without errors.

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

 

[crystalreporting]

We know.

Peter Shirley

http://www.linkcorp.com