ODBC Query Problem

wilhud

I have an ODBC query running in my page that looks like this...

$qryReport = "SELECT TOP 1 r.ReportID,r.ReportName,r.ReportCode,r.ReportHeaderText,r.ReportFooterText,t.TabCode FROM ((tblReports r LEFT OUTER JOIN tblTabs t ON r.TabID = t.TabID) LEFT OUTER JOIN tblUsersReports ur ON r.ReportID = ur.ReportID) WHERE t.TabCode = 'sales' AND ur.UserID = 1 AND r.ReportActive = 1";
$rsReport = odbc_exec($db_access,$qryReport) OR DIE("Unable to locate Reports table");
while (odbc_fetch_row($rsReport)) {
	$intReportID = odbc_result($rsReport,"ReportID");
	$strReportName = odbc_result($rsReport,"ReportName");
	$selReport = odbc_result($rsReport,"ReportCode");
	$strReportHeader = odbc_result($rsReport,"ReportHeaderText");
	$strReportFooter = odbc_result($rsReport,"ReportFooterText");
	$selTab = odbc_result($rsReport,"TabCode");
}

When I run the query and output the fields, I get this error...

Warning: odbc_result(): SQL error: [Microsoft][ODBC Microsoft Access Driver] The specified field 'ReportID' could refer to more than one table listed in the FROM clause of your SQL statement., SQL state S1000 in SQLGetData in report.php on line 33

ReportID should not refer to more than one table, since I call it directly using r.ReportID. Is there something else I may have done wrong? Any ideas or suggestions are appreciated.

Thanks,
Wil

bradgrafelman

Which one is line 33? Is it the query itself or the odbc_result() line for that field?

If it's the latter of the two, try replacing "ReportID" with "r.ReportID" in your odbc_result() call.

wilhud

The error actually is coming up for a completely different field. The ReportID call is no problem. It's not until this line that I have the error show up...

$strReportHeader = odbc_result($rsReport,"ReportHeaderText");

The line after that gives the same error, as well...

$strReportFooter = odbc_result($rsReport,"ReportFooterText");

Using the r.ReportID call in the odbc_result gives me an error saying it's not found. The weirdest part is that the error only happens on the results f these 2 fields and none of the others.

Wil