2 character field, list all if only 1 character entered

[cocopud]

I have a report in crystal reports v.10 which needs to be fixed and as I am new to crystal reports I am not sure the best way to approach it. One of the parameters in the report is called area and it contains 2 characters (ex. 1A, 1B, 1C, etc). If you enter 1A, the report works fine, but the users wants to be able to enter just the first character and return any results that start with that. For example if you enter 1 it should return all result with 1A, 1B, 1C, etc.

What type of coding is involved. It is easy enought to select all the records if I am in the database with a LIKE clause. Can I use the same thing in the report, or is there something better? Would this be done in the format field area, or somewhere else. Thanks

 

[kchaudhry]

Create a string parameter that accepts single values, and then use a record selection formula like:

{table.field} like "*"+{?parm}+"*"

Kchaudhry

 

[MCuthill]

Perhaps I am making this over complicated, but this question crossed my mind:
Would it be beneficial to create an [blue]IF[/blue] clause in your record selection based on the length of the parameter field? For example:

[blue]IF Length[/blue]({?Parameter}) = 2 [blue]THEN[/blue]
{table.field}={?Parameter}
[blue]ELSE[/blue]
{?Parameter} [blue]IN[/blue] {table.field}

As aforementioned, perhaps this is unnecessary and the [blue]LIKE[/blue] clause suggested above covers this circumstance, returning only those records which have the full 2 character area type.



Mike
______________________________________________________________
"It Seems All My Problems Exist Between Keyboard and Chair"

 

[cocopud]

Thanks for the input, but since I'm not real good with Crystal, it didn't work for me, and not sure what I did wrong.

In the like statement, what exactly is that doing? does it just allow for anything to be input in the 1st and 2nd character or only the 1st? There is already a parameter in the report called area, so in place of {?parm}, I put in {?area}, but it gives me a "field name is not known error"

Not sure where I went wrong.

 

[Madawc]

Crystal works by drag-and-drop, best to do it that way. Or use the 'wizards' when you are starting out.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Is {?area} set up to allow multiple values?

If multiple values are NOT allowed, then I would just use:

{table.area} startswith {?area}[1]

-LB

 

[cocopud]

I dont't think the startswith would work because there are more values than just starting with 1. There are values such as 2A, 2K, 3B, 3L, etc. So I need to be able to return either the 2 characters that are entered or everything that starts with the first character entered. Is there another way to use this with a wildcard character?

 

[lbass]

You have misunderstood. In this case, the [1] returns the first character of the parameter, so if the user entered "2A", the formula would return all records starting with "2". You didn't answer the question about whether you were allowing multiple values, and that is important to know.

But I'm wondering what you mean by

I need to be able to return either the 2 characters that are entered or everything that starts with the first character entered

Do you somehow hope to be able to sometimes have the specific second value matter and other times not?

-LB

 

[cocopud]

The parameter is not allowing multiple values. Since the report I am working with is very complex, I created a new report with just a few objects and tried the {table.area} startswith {?area}[1] formula. If I enter 3, it returns everything I need, for example 3H, 3K, etc; however, if just enter 3H it returns more than I need, for example, 3H, 3L, 3K, when I only want just the 3H's.

When I tried using {table.field} like {?parm}+"*" it seemed to give me the correct results, so now I have to try and get it to work in the current report.

 

[lbass]

So if they enter only 1 character, you want it to return all values that start with that character, but if they enter two, you want it to return ONLY the 2-digit value?

If so, then change the record selection formula to:

(
if len({?area}) = 1 then
{table.area} startswith {?area} else
if len({?area}) = 2 then
{table.area} = {?area}
)

-LB