Formula to Link Subreport retrieves only some data

[TheLibrary]

Crystal Reports 9

In the Main report I have a string field called NAME.
The text is usually 7 characters long, but sometimes 10 (carries extra irrelevant letters).
I have defined the formula LeftNAME as Left{NAME,7} in the Main report.

The Subreport database has matching data for only the first 7 characters of either the 7 or 10 length strings.

I have linked the Main "Fields to link to:" as LeftNAME and the "Subreport parameter field to use:" is ?Pm-LeftNAME.

Unless I also define the LeftNAME in the Subreport, it is not available in "Select data in subreport based on field:".

If I do or not, no data is retrieved when the original NAME is 10 characters.
Works fine when NAME is 7 characters.


With the LeftNAME formula in both the Main and Subreport
"{@LeftNAME}={?Pm-@LeftNAME} and"
appears in the record selection formula in the subreport.


Is there an alternate way to get the subreport to retrieve data based on the main report formula noted?

Would the use of Shared Variables solve the problem better?

 

[synapsevampire]

Perhaps there's a subtle difference in these fields, such as uppercase or spaces?

You might have to add in a trim/uppercase

I'd create SQL Expressions (Insert->Field Object->Right click the SQL Expressions and select New) for the tables referenced by both the main and subreports as it will increase performance, and then make the link based on those.

This is database dependent, but try:

left(field,7)

-k