Yep still need parameter selection help w dsicreet or range

[muffntuf]

I still am not getting anything back. They want to be able to just take a partial part number like

151421 is the part number they just want to enter 15 and get all the records back that start with 15.

k said -

Make the parameter a range type, and set the default value to " " (a space).

In the Report->Record Selection->Record place something like:

if isnull({?part})
or
trim(maximum({?Part})) = ""
then
{Customer.part} like "*"& minimum({?Part}) & "*"
else
if {?Part} = "2" then
{Customer.part} >= minimum({?Part})
and
{Customer.part} <= maximum({?Part})

So if they only enter one value (the lower range), it will search for one, if they enter the full range, it will search for >= the first value entered and <= the second value.

This will also pass the SQL to the database so performance should be fine.

My response-

If they need all of the records to come back do I still need to add an 'all' statement somewhere in the mix?


{?Part Number} = "All" or {@Part Number} ={?Part Number}


This is what I put in, if I choose the "" I get all records back, if I choose a range with just a couple numbers off the front end of the part number I get zilch.
Here's what I did:
(if isnull({?Part Number})
or
trim(maximum({?Part Number})) = ""
then
{@Part Number} like "*"& minimum({?Part Number}) & "*"
else
if {?Part Number} = "2" then
{@Part Number} >= minimum({?Part Number})
and
{@Part Number} <= maximum({?Part Number}))


Oh and the part number is formulated like this because of the paritial bit,

trim({@CommonPartNum}) [1 to 6]




k said -

Make the parameter a range type, and set the default value to " " (a space).

In the Report->Record Selection->Record place something like:

if isnull({?part})
or
trim(maximum({?Part})) = ""
then
{Customer.part} like "*"& minimum({?Part}) & "*"
else
if {?Part} = "2" then
{Customer.part} >= minimum({?Part})
and
{Customer.part} <= maximum({?Part})

So if they only enter one value (the lower range), it will search for one, if they enter the full range, it will search for >= the first value entered and <= the second value.

This will also pass the SQL to the database so performance should be fine.

My response-

If they need all of the records to come back do I still need to add an 'all' statement somewhere in the mix?


{?Part Number} = "All" or {@Part Number} ={?Part Number}


This is what I put in, if I choose the "" I get all records back, if I choose a range with just a couple numbers off the front end of the part number I get zilch.
Here's what I did:
(if isnull({?Part Number})
or
trim(maximum({?Part Number})) = ""
then
{@Part Number} like "*"& minimum({?Part Number}) & "*"
else
if {?Part Number} = "2" then
{@Part Number} >= minimum({?Part Number})
and
{@Part Number} <= maximum({?Part Number}))


Oh and the part number is formulated like this because of the paritial bit,

trim({@CommonPartNum}) [1 to 6]

Try:

(
if {?Part Number} <> "All" then
(
if isnull({?Part Number})
or
trim(maximum({?Part Number})) = ""
then
{@Part Number} like "*"& minimum({?Part Number}) & "*"
else
if {?Part Number} = "2" then
{@Part Number} >= minimum({?Part Number})
and
{@Part Number} <= maximum({?Part Number})
)
else
if {?Part Number} = "All" then
true
)

Now they can enter All or you can have it as an additional default value as well.

-k

 

[synapsevampire]

Ahhh, my apologies, the >= and <= won't work here I'm afraid.

I guess I'm not sure how one would use a range with a like because you want to use LIKE but you also need a >= <=

You might be able to builod a SQL Expression to pad out the partnumber with "00000000000000" or some such, that way they are all the same length, then the >= and <= against that should work.

-k

 

[muffntuf]

Is there some way I could take a loop approach to this in the select criteria, like we can do for displaying this type of set up? I am just not sure how to modify this to get the like or startswith in here.
For example:

global numbervar increment;
//increments the loop so that all parameter entries can be displayed

global stringvar output := "";
//creates a string "running total" so that all entries can be displayed

for increment := 1 to count({?Project Number}) do
(
if minimum({?Project Number}[increment]) = maximum({?Project Number}[increment])
then (output := output + (Minimum({?Project Number}[increment]) + chr(10))
else (output := output +(Minimum({?Project Number}[increment]) + ' to '
+ (Maximum({?Project Number}[increment])) + chr(10);
);
);
output;

 

[synapsevampire]

It'll never pass to the database.

Is there some reason why you don't like the SQL Expression approach? It will pass to the database.

Also what happened when trying my last example?

I tried the following in the crystal example report: Account Statement

if maximum({?Part}) = "" then
{Customer.Address1} like "*"& minimum({?Part}) & "*"
else
if {?Part} = "2" then
{Customer.Address1} >= minimum({?Part})
and
{Customer.Address1} <= maximum({?Part})

Works fine and passes to the database.

-k

 

[muffntuf]

Did I miss something K about, I thought this was supposed to go into the select criteria not sql expressions. dont' have a problem with sql expressions, just don't know what I would be doing there.

Thanks,
muffntuf

 

[synapsevampire]

Myt last post spoke of the SLQ Expression notion.

At any rate, what doesn't work about the other technique?

Like I said, it works here.

-k

 

[muffntuf]

Huh I just put this into the same report as you and I am not seeing it work. I included the "All" portion in the parameter and in the select statement:

{Orders.Order Amount}>0 and
{Orders.Shipped}=Yes and
if {?Part} <> "All" then
(
if maximum({?Part}) = "" then
{Customer.Address1} like "*"& minimum({?Part}) & "*"
else
if {?Part} = "2" then
{Customer.Address1} >= minimum({?Part})
and
{Customer.Address1} <= maximum({?Part})
) and
not {Orders.Payment Received}

What am I missing?

Muffntuf

 

[muffntuf]

What does this part of the code do for the statement?

if {?Part} = "2" then

Thanks,
muffntuf