"A string is required here" error in Formula

Hello,

I am getting the above error when I run this formula:

{vw_reporting_biannualaudit.Drug Schedule}={?@Drug_Schedule}

and  

if  isnull({?@Drug_Schedule}) or not numerictext({?@Drug_Schedule}) then
       0
else
       tonumber({?@Drug_Schedule})

The last part of the formula (tonumber({?@Drug_Schedule}) gives the error.

I have set the @Drug_Schedule parameter to number.

If I set the @Drug_Schedule  parameter to a string it will give me the error "A number is required here" at the top of the formula for the @Drug_Schedule so either way I get an error.

Thanks for any help,

Mike
Michael GrahamData AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
Where are you trying to use this formula?

Are there 2 formulas there or is the full formula

{vw_reporting_biannualaudit.Drug Schedule}={?@Drug_Schedule}

 and  

 if  isnull({?@Drug_Schedule}) or not numerictext({?@Drug_Schedule}) then 
        0
 else
        tonumber({?@Drug_Schedule})

Open in new window


If the parameter is a number then you can't convert it to a number.
If it is a number then it can't have non-numeric text in it.

What are you trying to do with the formula?  If what I put in the text box is the full formula then you will get another error because the AND wants a Boolean.

If there are 2 formulas then change the second one to

 if  isnull({?@Drug_Schedule}) then 
        0
 else
        {?@Drug_Schedule}

Open in new window


mlmcc
0
Michael GrahamData AnalystAuthor Commented:
I am still trying to achieve the Parameter Drug Schedule to return all records when nothing is entered.  This is very easy when I set the Parameter to optional.  I am only using 1 parameter for Drug Schedule now.

We are new to automation in Crystal Reports via Business Objects.

On the dashboard the Parameter will appear at the top and nothing needs to be entered which is what I want (again if I set the parameter to optional prompt).

However, when I run the report it will prompt me to enter a value like it would in the native crystal report.  I do not have to enter a value (I can leave it blank) but I do not want this prompt to show up.
0
mlmccCommented:
So this is the selection formula?

I believe you will need to either have no parameter or write your own viewer.

Are you running the report in the dashboard or separately?

mlmcc
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Michael GrahamData AnalystAuthor Commented:
Yes this is the selection formula.

I am running it in the dashboard - however I am only allowed to edit it in the report.

I am not sure how to run my own viewer
0
mlmccCommented:
This selection formula will get you what you need


(
Not HasValue({?@Drug_Schedule})
)
OR
(
{vw_reporting_biannualaudit.Drug Schedule} = {?@Drug_Schedule}
)

You can't get around the parameter prompt

mlmcc
0
James0628Commented:
mlmcc has probably given you your best solution.

 FYI, there were a few problems with the formula in your first post.

 Assuming that {vw_reporting_biannualaudit.Drug Schedule} is a numeric field, then you can't just compare it with a string value.  That's why you got that error on the first part when the parameter was a string.  You would need to convert the parameter to a number (or the field to a string, but then you'd have to worry about the formatting of the string, to make sure that the strings match).

 If you're going to use IsNull, you need to do that before you do anything else with that field (or parameter).  CR generally does not handle null values well when evaluating formulas.  If it encounters one outside of the IsNull function, it just stops evaluating the formula at that point and produces a null result.  So, you need to use IsNull to test for a null first, and then use the field (or parameter) for other things (eg. to compare with a field) after that.  Technically, since this is a record selection formula, I guess that might not be a problem if CR is just passing those tests to the server and not actually evaluating them locally, but there's no point in taking chances.  So, the IsNull test should be first.

 Likewise, you had ( not numerictext({?@Drug_Schedule}) ), but you did that after you compared the field with the parameter.  So, if the parameter was not numeric, you'd try to compare it with the field first, get an error, and never get to the NumericText test.  If the parameter might not be numeric, you should use NumericText to check that before you compare the parameter with the field.

 Also, your if-else that produces 0 or ToNumber ({?@Drug_Schedule}) doesn't make any sense, since it produces a number by itself after the And.  Now that I think about it, I think maybe what you were going for there was something like this:

{vw_reporting_biannualaudit.Drug Schedule} =
(
if  IsNull ({?@Drug_Schedule}) or not NumericText ({?@Drug_Schedule}) then
       0
else
       ToNumber ({?@Drug_Schedule})
)

Open in new window


 However, if you want a null or non-numeric parameter to include all records, then that won't work.

 If the parameter is null or not a numeric string, it compares the Drug Schedule field with 0.  Assuming that Drug Schedule is never 0, you will get no records.
 If the parameter is a numeric string, it converts the string parameter to a number and compares the field with that value.

 If you want a null or non-numeric string parameter to include all records, then I think you could use:

(IsNull ({?@Drug_Schedule}) or not NumericText ({?@Drug_Schedule}) or
{vw_reporting_biannualaudit.Drug Schedule} = ToNumber ({?@Drug_Schedule}) )

Open in new window



 FYI, when it comes to converting a string to a number, you might also want to look at the Val function.  Val is similar to ToNumber, but it just takes any leading numeric characters and converts them to a number, and ignores anything after that.  If the leading characters are not numeric, it doesn't give you an error.  You just get 0.  But if the field/parameter could be null, you'd still need to use IsNull to test for that first.

 James
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Michael GrahamData AnalystAuthor Commented:
Thanks everyone I finally figured it out - I had to keep working on the formulas you gave me.  You have taught me a lot while I am just starting out in Crystal.  I appreciate all the help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.