I've been given the task of converting some reports from Access to Crystal Reports, I've used Crystal before but just for building basic reports, but to make it quicker I wanted to convert the SQL from Access rather than go through the interface and drag fields over etc to replicate the reports. The characters used etc are a bit different but I think eventually I've got it mostly sorted for my test report.

I'm working in Add Command in my DB connection, and I've entered the code as below, and I can get the report to work as expected and bring me back my fields and values, however in the HAVING clause, T13A07 in the report as below, should be a parameter where the user is prompted to enter the match code. I've defined a parameter as MatchCode on the right of the Add Command pop-up, entered it into the report as ={?MatchCode} (so the line is HAVING (((MD009.MTCD09)={?MatchCode})) )and when I try to run the report it correctly prompts for the parameter, I enter T13A07 and it gives me an error saying 'Failed to retrieve data from the database. Details: 42S22:[IBM]...SQL0206 - Column T13A07 not in specified tables. [Database Vendor Code: -206 ]'

Any help would really be appreciated, I can't see why it would work without the parameter but won't work with the parameter with the entered value or why it thinks it's a column heading and not a field value. I'm pretty new to Crystal Reports so apologies is this is a really dumb question.


SELECT "MD009"."MTCD09" AS Code, Trim("MTDS08") AS "Match", Count(MD009.MEMB09) AS "Count"
FROM "TKTDTA"."MD009" "MD009" LEFT JOIN "TKTDTA"."MD008" "MD008" ON ("MD009"."ACTR09" = "MD008"."ACTR08") AND (MD009.CONO09 = MD008.CONO08) AND (MD009.MTCD09 = MD008.MTCD08)
WHERE (((MD009.ACTR09)='A') AND ((MD009.CONO09)='RAN') AND ((MD009.SRCE09)<>'C' Or (MD009.SRCE09)='C') AND ((MD009.PYRF09)<>0) AND ((MD009.STAT09)='1'))
GROUP BY MD009.MTCD09, Trim("MTDS08")
HAVING (((MD009.MTCD09)='T13A07'))