order by when you have joined tables

[livvie]

If I use order by on a query that has two linked tables I keep getting an error:
This is the query
SELECT d.PDetailsID, d.po_id, d.po_uniqid, d.job_id,
d.p_qnty, d.p_desc, d.p_unitp, d.p_units,
d.p_extend, d.p_qos, d.p_grec, d.p_done,
d.cocreq, d.est_id, d.line, d.class,
d.selected, d.itemid, d.category, d.GroupID,
d.p_qosextended, d.late, d.lastsavedby, d.matid,
d.customer, SQLACCESS.tblctpur_d.jobid, d.DTime, d.Notes,
d.DescDetails, h.pur_ddue
FROM SQLACCESS.tblctpur_h as h INNER JOIN
SQLACCESS.tblctpur_d as d ON h.pur_id = d.po_id
ORDER BY d.line

The error is :
The colum prefix d does not match with a table or alias used in the query

 

[mparker1113]

SELECT d.PDetailsID, d.po_id, d.po_uniqid, d.job_id,
d.p_qnty, d.p_desc, d.p_unitp, d.p_units,
d.p_extend, d.p_qos, d.p_grec, d.p_done,
d.cocreq, d.est_id, d.line, d.class,
d.selected, d.itemid, d.category, d.GroupID,
d.p_qosextended, d.late, d.lastsavedby, d.matid,
d.customer, SQLACCESS.tblctpur_d.jobid, d.DTime, d.Notes,
d.DescDetails, h.pur_ddue
FROM SQLACCESS.tblctpur_h as h INNER JOIN
SQLACCESS.tblctpur_d as d ON h.pur_id = d.po_id
ORDER BY d.line


Right-- d is an alias for a table, so you have to say : SELECT d.stuff from SQLACCESS.tblctpur_d as d INNER JOIN SQLACCESS.table_h as h on h.prodID

Something like that-- you get what I mean, right?

 

[livvie]

IS that not what I did?

 

[mparker1113]

IS that not what I did?

Oops-- that is what you did.. sorry.

Are you sure that you have the name of the table right?

 

[livvie]

Yes sure - it's fine if I remove a table or remove the order by

 

[livvie]

It used to be called lineno and that forced me to use [] it didn't work that way so I changed it to line - I have used lineno elsewhere in my db and it is fine.

 

[livvie]

To make this worse this doesn't seem to be confined to one set of tables - it is also occuring on another query. The queries run fine in the Query Grid but it's when I try to open the form that uses the query I get the error.