Subreport won't find records 1

[drwunz]

I'm using CR8.5 and SQL Svr2000, and am having trouble with a report having a subreport...
The main report record select statement is:

({Event.Description} like '*' & {?BarCode} & '*') and {Event.Source} = "FCC"

and the subreport record select statement is:

{ContainerHistory.ContainerID} like '*' & {?Pm-?BarCode} & '*'

The barcode, a 20-digit number, is embedded in the Event.Description field (varchar 100) and it is the value placed in ContainerHistory.ContainerID (char 20)

Given a partial barcode, both reports work fine...given the full 20-digit barcode, the subreport does not find any records. I've checked the tables and the barcode is correct in both. In fact, both of the following select statements find the records when run in Enterprise Manager:

SELECT *
FROM ContainerHistory
WHERE (ContainerID LIKE '%00000111110098499336%')

SELECT *
FROM Event
WHERE (Description LIKE '%00000111110098499336%')

Why won't the subreport find anything if given the full 20-digit number?

 

[lyanch]

I would look at the actual SQL being created in the subreport (database -> show SQL query). It may be doing something silly. You could try something in the subreport SQL selection such as

if length({?Pm-?BarCode}) = 20 then
{ContainerHistory.ContainerID} like {?Pm-?BarCode}
else if not (length({?Pm-?BarCode}) = 20) then
{ContainerHistory.ContainerID} like '*' & {?Pm-?BarCode} & '*'

Lisa

 

[drwunz]

Thanks!
Show SQL Query showed exactly what happened. Since the ContainerID field is defined 20 characters, and I wanted to test against '*' & '00000111110098499336' & '*', which is 22 characters, CR truncated it to '%0000011111009849933'. This didn't match any records.
Your suggested statement did the job.
Thank you, very much!
don