Failed to retrieve database error only when grouping on SQL Query

[jj1576]

Hi,

I am trying to group my report on the following SQL Expression named %empl

(
select max(B.`ResultText`)
from TestMainList A, TestResults B
where A.`TestID`= B.`TestID` and
A.`Description`= 'Employee' and
B.`TestDate` = TestResults.`TestDate`
)


However, when I run the report, it gives me the error "Failed to retrieve data from database" and Database Vendor Code -3100- it mentions some kind of syntax error even though all fields are correct.

The report runs fine if I add %empl to the details section. It even runs fine if I group it by %empl AFTER first running the report with the %empl expression in the details. However, when I try to re-run the report, it gives me the error again.

The SQL expression is error free when I check it. I am using an Access database, with an ODBC connection. Has anyone else experienced this? Thanks!

 

[Turkbear]

Hi,
Are you using that as a SQL expression within a report section or as a Command instead of selecting from a table?

Also, tht error:

http://msaccess.blogspot.com/2005/10/vba-error-codes-and-messages.html[/URL]]
3100 Can't set field 'Item' in join key to Null.

May indicate that TestID in one or both tables is NULL.







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[jj1576]

TurkBear,

Thanks for the quick response. I haven't looked at the whole database for that field, but it is possible that one of the records is null- interestingly, I only get the error whenn grouping on the field.

Also, I am using the expression as a sql expression within the report. Is there a work around for the null field if that is the case?