Parameters with wildcards

[bluevelvet]

Please help before I lose my sanity!!! I am trying to use wildcards with parameters so that when end users are prompted for a part no for example, they can put in C69-* where * represents the missing part of the number. I have tried using the following syntax in my record selection formula:

Example 1.

{table.column} like["*"] or {table.column}={?parameter}

Example 2.

if {?parameter}="*" then {table.column}like {?parameter}
else ({table.column}={?parameter}

By the way I'm running version 9 of Crystal.
I would appreciate any help at all on this and also if anyone knows of a programmers reference I could buy.....

 

[mbarron]

If they are only entering the first part of the number, you can use:

{table.column} startswith{?param} Mike

 

[lyanch]

{table.column} like {?parameter}

is what I use when allowing wildcards. You can check to see if "*" is part of the {?parameter} string so that you can use the "=" if they don't, but it isn't necessary unless using the wildcard causes a big performance hit.

Lisa

 

[mbarron]

Lisa is right. When I first tested mine with the "like", it didn't work. Or I didn't do it correctly, which is probably more accurate. Mike

 

[Naith]

You don't use StartsWith in conjunction with Like. You use it instead of it.

They both translate to the SQL in the same way.

Naith

 

[mbarron]

If you're referring to my post... I didn't try to use them together. I probably missed-typed the item I used for my test. Ya can't find what ain't there.

I just tried to combine them . Crystal "yells" at you if you try to combine the functions. The standard "A number, currancy amount .... expected here" error. Mike

 

[bluevelvet]

thanks for all the replies... I tried to use {table.column}like{?para} but it generated an error saying a string is required here???? :-(

 

[Naith]

The datatype of the parameter needs to match the datatype of {table.column}. That is to say; they should both be strings, as you can't use '*' as a wildcard operand with any other datatype.

Naith

 

[bluevelvet]

Both the column and the parameter are strings so I'm at a loss as what is going wrong here.

 

[Ngolem]

so if I understand you....you want the user to enter part of a number and receive all PN's containing that part.

try this maybe

{?Partial Number} String parameter (use NO wildcard char)

in the record selection formula

instr( {table.PartNumber},{?Partial Number} ) <> 0 ;

maybe this will work Jim Broadbent

 

[bluevelvet]

thanks everyone, got it working but still don't know what the problem was cos in the end I recreated my report and the {table.col}like{?para} worked. Corrupt report perhaps. I will make a note of all the solutions offered for future reference...

thanks again

 

[Tolambo]

Ngolem,
That was a useful piece there.
To avoid the proverbial Error Message "String Missing here.." one must make sure that the Parameter {?Detail} is defined as a string and that it is not a range. Crystall (with ACCPAC) usually will only allow you to use that code if the detail is a discrete value.
Happy scripting!
Regards