Filtering causes "The column prefix '..' does not match with a table name or alias used in the query

  • Thread starter Thread starter MNF
  • Start date Start date
M

MNF

I am using Access 2002 ADP and found that if report has record source
created/modified using QUERY DESIGNER and the query has ORDER BY
statement,
opening the form/report with a filter causes "The column prefix
'TableName' does not match with a table name or alias used in the
query"
For example if select query is something like

SELECT AUTHORITY_COURSE.Course, Pick_Slip_Footer.PatronID,
Pick_Slip_Footer.Barcode,Pick_Slip_Footer.DateShipped
FROM AUTHORITY_COURSE INNER JOIN Pick_Slip_Footer ON
AUTHORITY_COURSE.CourseNo = Pick_Slip_Footer.CourseNo
ORDER BY DateShipped DESC

Access query designer inserts TABLE qualifier in the order statement,
so if you open the query next time, it will be shown as

ORDER BY Pick_Slip_Footer.DateShipped DESC

When report is opened, Access genetates filtered query (using DRVD_TBL
sub-select query) and the error occured.
The only workaround is after using Query Desinger manually remove
TABLE qualifier in "ORDER BY" clause.

Is MS Access development team aware about this behavior?

I found that many people in news groups complained about this error
e.g. http://groups.google.com.au/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=#thwef9HBHA.1948@tkmsftngp02

Regards,
Michael Freidgeim
 
In the mean time try the (unsupported & undocumented) order by ordinal
posisition
ie
ORDER BY 1
instaead by <COLUMN_NAME>

Pieter
 
Back
Top