Using PART of static parameter TEXT for Select Statement

[BeeBear]

Hi,

My problem is with trying to select data based on the right(X) characters of a Static parameter, where the "X" is not a definitive number.
I want the list of parameter options in the drop-down list to be a concatenation of the description and code fields, but only use the CODE part of the list to select data on.

As the crystal reports (XI) are accessed through an embedded third party software, This takes some of the parameter functionality out, so I have to use static parameters, and dont have the normal option of listing the CODE for the parameter, but displaying the DESCRIPTION to the user.

EG:
Parameter {?LOCATION} displays on drop-down list as:

"Bendigo"
"Ballarat"
"Geelong"
"Melbourne"

When using {?LOCATION} in the select statement, I have to use
{LOCN.DSCR} = {?LOCATION} , rather than
{LOCN.LOCN_CD} = {?LOCATION}, which is not good for the reports functionality and speed.

As the user doesnt know (or want to know) the CODES, they only want to select on the Descriptions in the {?LOCATION} parameter drop-down.

If I concatenate the DESC and CODE Fields in the parameter List as follows, the user can still pick based on the DSCR:

"Bendigo BE"
"Ballarat BA"
"Geelong GE"
"Melbourne MEL"


The only problem, is the code on the end, is not always the same number of characters, so I cant use a basic RIGHT({?LOCATION} ,2) to get the results needed.

EG

{LOCN.LOCN_CD} = Right({?LOCATION},3)

What I want to do is put say 5 spaces between the DESC and the CODE parts of the parameter list, and use these 5 spaces as a separator.
What formula would I use to look for the 5 spaces and then start using the parameter AFTER that group of 5 spaces????

Thanks HEAPS for any assistance




regards

BB

*** Count your blessings not your problems******

 

[Turkbear]

Hi,
How about using the MID function, together with an InStr to use all characters after a space - you would then use just 1 space between the desc and the code, like this selection formula:

{LOCN.LOCN_CD} = Mid({?LOCATION},Instr({?LOCATION," ") +1)

Optionally you could use a separator character instead of a space like
"Bendigo_BE"
and use the InStr to find the _ character





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

I think you could just use:

trim(right({?LOCATION},3))

This would remove the space if it appears in the 3-character segment.

-LB

 

[BeeBear]

Hi,

Thanks for your responses. Great help.

In this instance the Mid({?LOCATION},Instr({?LOCATION," ") +1) works better than the TRIM(RIGHT), because of the variable length of code can be between about 2 and 9 characters, so rather than put lots more spaces and then trim, its easier this time to do the Mid & Instr formula.

I did however need to change it slightly to
trim(mid({?Param5},instr({?Param5}," ") +1))
because it still showed the last 2 spaces of the 3 I had put between the description and the code


Cheers


regards

BB

*** Count your blessings not your problems******