Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Parameter Mask

Status
Not open for further replies.

genekr

Technical User
Jul 19, 2005
45
US
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
 
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
 
That should be
Code:
mid({fieldname}, 4, 2) = {?parameter}
- position 4, for a length of 2.

You could also try
Code:
{?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.

[yinyang] Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10 [yinyang]
 
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
 
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 ?
 
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.

[yinyang] Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10 [yinyang]
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top