Formula Help Needed for Number Parameter 2

[dbielenda]

I have this formula:

(if {?Order Number} = ""
then true else {?Order Number} = {TABLENAME.FIELD})

If the {?Order Number} parameter is left blank, then give me all Order Numbers to show on my report, otherwise just give me the {?Order Number} parameter that the user typed in. If the user typed in the Order Number 14705, just show this Order Number on the report.

Crystal tells me, "A number is required here" and the cursor is placed right before the "" in my forumla. I have used this formula for string values and it works just fine. Why do I get the error when it's a number value?

Thank you! s-)

 

[mmaz]

What is your parameter data type? A number or a string?

 

[dbielenda]

The parameter data type is a number.

 

[mmaz]

Then try:
(if IsNull({?Order Number})
then true else {?Order Number} = {TABLENAME.FIELD})

 

[dbielenda]

I tried this formula and I get "A value must be entered here" when I refresh the report and try to leave the {?Order Number} blank. When I place a 14705 in the parameter, it works and returns only 14705 Order Number's.

 

[mmaz]

Humm. It seems that if the parameter is of data type "Number", it is required, but if the datatype is a string, then you can leave the entry field empty.

So maybe you should change the datatype to string, and in your formula, do something like:

(if IsNull({?Order Number})
then true else {?Order Number} = CStr{TABLENAME.FIELD}))

 

[Nuffsaid]

Hi,

Can you change your {numeric.field} to text with
totext({numeric.field}, then make your parameter a text field, without screwing up the rest of the report?

If so, by what you said earlier, Problem Solved !!

Nuffsaid.

 

[dbielenda]

mmaz,

This formula left the entire report blank in both cases when I left the {?Order Number} blank and entering 14705.

Nuffsaid,

The forumla using ToText allowed to leave the {?Order Number} blank and returned all Order Numbers but when I placed 14705 in there, it gave me a blank report. I tried this ToText once before and it just did not to the trick.

Thank you both!
s-)

 

[mmaz]

Okay, I got it. I tested this in CR v.8. The parameter data type is string, and I put the following in my record selection formula:

if {?OrderNumber} <> &quot;&quot; then
{TABLENAME.FIELD} = ToNumber({?OrderNumber})
else
{TABLENAME.FIELD}= {TABLENAME.FIELD}

 

[Nuffsaid]

Ok,

So what would happen if both fields are converted to text and you used a &quot;like&quot; statement in your selection formula?

{?Order Number} like totext({TABLENAME.FIELD})

{?Order Number} is a string.

I would think that entering a * at the prompt would return all records and entering a specific &quot;string&quot; would just return that record.

Nuffsaid.

 

[Ngolem]

It is simpler with numeric values to indicate a range of numbers with 2 parameters defaulted to unreasonably high and low values to catch ALL

ie {?Start Order#} (numeric)defaulted to 00000000
{?End Order#} (numeric)defaulted to 99999999

when user wants 1 order# he types in the same value twice....also get the benefit of reporting on a range of order numbers rather than 0ne or ALL

record selection is easy too

{TABLENAME.FIELD} >= {?Start Order#} and
{TABLENAME.FIELD} <= {?End Order#} and

Jim

 

[kenhamady]

I would give {?Order Number} a default value of 0, and then use :

(if {?Order Number} = 0
then true else {?Order Number} = {TABLENAME.FIELD}) Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[dbielenda]

mmaz,
Using this formula when I left the {?Order Number} blank, it did not return all the Order Number's. When entering in 14705, it worked. Why do you think that when leaving the Order Number blank that it did not return all? I see that on my report some Order Numbers are left blank, could this be a reason why?

Nuffsaid,
I tried the like formula and it left my report blank when using the * and by entering in 14705 Order Number. When I switched the formula to say:
totext({?Order Number} like {TABLENAME.FIELD), using the * still did not return all the values but entering in 14705 Order Number worked.

Ngolem,
I am still testing this one.

Ken,
I tried your formula and when I refreshed my report, Crystal tells me, &quot;You must enter a number here.&quot; I set the value type to be a number.

Thank you all for your help! I appreciate the quick feedback! s-)

 

[dbielenda]

Ken,

Your formula will give all (including those blank Order Numbers) when I enter a 0 (zero) in the parameter. I think this will do it for now. I am still wondering how to use mmaz's formula to include those blanks.

Thank you soooo much! s-)

 

[mmaz]

I did not have any blank values, so I did not run into that problem. But here's a solution that works if you have blank values:

if {?OrderNumber} <> &quot;&quot; then
{TABLENAME.FIELD} = ToNumber({?OrderNumber})
else
IsNull({TABLENAME.FIELD}) or
{TABLENAME.FIELD}= {TABLENAME.FIELD}

 

[dbielenda]

You are the best mmaz! It worked great. Thank you soooo much! s-)