Parameter Mask

[genekr]

Is there a way to take a field, xxx4402xxx, and in the parameter select only char's 4,4 to run the report on?
I thought I could do it with mask, but am having some difficulty.

Thank you.
Gene

 

[IanWaterman]

YOU can use this as your select formula

mid({fieldname},4,4) = {?parameter}

Depending on your CR version, pre CR9 would not resolve formula and brought back all data to local PC, CR9 and above converts above formula to correct SQL and reduces data selected on database server.

Ian

 

[Madawc]

That should be

mid({fieldname}, 4, 2) = {?parameter}

- position 4, for a length of 2.

You could also try

{?parameter} in {fieldname}

This would get values like xy44yy8 and tjrkertj44 - is that what you want?

To make your own investigation of these tests, put them first as a 'boolian', a formula field that returns 'True' or 'False'. Do this without selecting data, to be sure you have it right. Then include the formula field name in the selection, and it will work on that.

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

 

[synapsevampire]

If you have CR 8 or above, consioder using a SQl Expression using a SUBSTR to parse the data, then you can reference the SQL Expression in the reocrd selection formula.

Ian: I believe that some ODBC drivers will not parse and pass the SUBSTR in CR 9 and above, but in general it's true that Crystal does a better job of passing sql to the database in CR 9 and above.

-k

 

[genekr]

Thanks all. I'll try it.

Gene

 

[genekr]

I may not have been specific enough. CR9, PSQL9, W2003 server.

The user is only going to know the 4 digit number, not the whole string. Is it possible to have them enter a parameter of 4402 and have the report select file xxx4402xxxx ?

 

[Madawc]

Yes, {?parameter} in {fieldname} would work. That's assuming the position of the string doesn't matter: if it does matter, that would be more complex.

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

 

[lbass]

Ian's formula would do exactly what you want. I wouldn't use {?parameter} in {fieldname}, since if there were other numbers in the field or if they entered only three numbers, e.g., it could pick up the wrong records.

-LB