×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

"A number is required here" Error

"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.

RE: "A number is required here" Error

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

RE: "A number is required here" Error

Just in case, try:

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

-k

RE: "A number is required here" Error

(OP)
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.

RE: "A number is required here" Error

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

RE: "A number is required here" Error

I think SV meant:

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

-LB

RE: "A number is required here" Error

Or..

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

Peter Shirley
http://www.linkcorp.com

RE: "A number is required here" Error

(OP)
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.

RE: "A number is required here" Error

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

RE: "A number is required here" Error

(OP)
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

RE: "A number is required here" Error

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;


RE: "A number is required here" Error

(OP)
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?

RE: "A number is required here" Error

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

RE: "A number is required here" Error

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

RE: "A number is required here" Error

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.

RE: "A number is required here" Error

(OP)
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.

RE: "A number is required here" Error

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

RE: "A number is required here" Error

(OP)
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! :)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close