Problem with String

[toshii1222]

Hi all, I will try to explain the problem I have as clear as possible.

I have String variables in my data as following.

02
2a
2b
03
3a
3b
04
4a
4b

2a and 2b are the subcodes for 02; 3a and 3b are the subcodes for 03; 4a and 4b are the subcodes for 04

Here is my problem, when I try to use the range parameter to show codes between 03 and no upper bound I will get the following result.

03
04
2a
2b
3a
3b
4a
4b

But the result I have in mind should be..

03
04
3a
3b
4a
4b

Can anyone help me with this problem?
Thanks.

 

[synapsevampire]

I see.

That's one fishy database design, get out the scaler and go interrogate your dba...

I would create a formula to properly identify data in it's natural order, such as:

// @SortableField formula
if not(isnumber(mid({table.field},2,1))) then
"0"+{table.field}
else
{table.field}

Now your selection will work, the only difference is that at display of the field you'll want:

if len(@SortableField) = 3 then
mid(@SortableField,2,2)
else
@SortableField

Should work OK.

You also might do this on the database usi8ng a SQL Expression, but we'd have to know your Crystal version and database type to use advanced features like that.

If the table is relatively small, you should be OK with this.

-k

 

[toshii1222]

Thank you.

I am sure the formula you showed me will work for the range 03 to no upper bound. But I don't think it will work when the user try to run the range of 2a to 2b.

Is there a solution for it? or is it just impossible?

I am using CR version 8.

Thanks

 

[synapsevampire]

That should be considered a subrange, and I assume that you mean that this is using the same parameter?

The whole concept of a hierarchy seems to be lost here.

You might be better served by 2 fields, and I would think 2 parameters.

You could use a Union Query to create this fairly easily. Again, you have a poor database design and I would attempt to address that.

At any rate, you can certainly code for a 2a - 2b parameter range by deriving the record selection formula:

If not(isnumber(mid({?Parameter},2,1))) then
left({table.field},1) = left({?Parameter}[1],1)
or
{table.field} = {?Parameter}

Something like that, it might prove a bit more complicated, but the theory is sound.

-k

 

[toshii1222]

Thank you. I will give it a try.