Sorting a Form

[Chris]

I have the following SQL Query and am trying to sort the display based on
[D-C Reference]. The [D-C Reference] is a text field as it contains
alpha-numeric characters. I would like the form to ascend A-Z. I tried
adding the ORDER BY clause after the FROM Tbl_PIP field and no luck.
Currently the results sort in the order the were entered into Tbl_PIP. Any
suggestions or help would be appreciated.

SELECT Tbl_PIP.InspectionID, Tbl_PIP.[Hotel ID], Tbl_PIP.Date, Tbl_PIP.Need,
Tbl_PIP.Item, Tbl_PIP.Category, Tbl_PIP.Location, Tbl_PIP.[D-C Reference],
Tbl_PIP.Comment, IIf([Need]=True,True,"") AS Need2, Tbl_PIP.Phase,
Tbl_PIP.PIPID, Tbl_PIP.[Top Priority], Tbl_PIP.[Required Product Reference]
FROM Tbl_PIP
WHERE (((Tbl_PIP.Item) Is Not Null) AND
((Tbl_PIP.Category)=[Forms]![Frm_PIP_Inspection]![cbo_Area]) AND
((Tbl_PIP.Location)=[Forms]![Frm_PIP_Inspection]![cbo_Location]) AND
((IIf([Need]=True,True,"")) Like
[Forms]![Frm_PIP_Inspection]![Frm_PIP_Inspection_Subform].[Form]![Text25] &
"*"));

 

[BruceM]

Date is a reserved word. It should not be used for a field or control name,
but if it is it must be enclosed in square brackets. The following links
are to lists of reserved words:
http://support.microsoft.com/default.aspx?scid=kb;en-us;209187
http://support.microsoft.com/kb/248738/EN-US/
http://office.microsoft.com/en-us/access/HP011353121033.aspx

The Issue Checker here is a wonderful utility that will check your database
for such problems and many others:
http://www.allenbrowne.com/AppIssueBadWord.html

Try opening the query in design view and adding Ascending for the sort order
for [D_C Reference]. If it works, see where ORDER BY occurs in the SQL.
You have stated that you had "no luck" using ORDER BY after FROM Tbl_PIP, by
which I can only assume that nothing changed in the sort order. I would
have expected ORDER BY at the end of the SQL, but I don't know if that
matters, and I'm not positive of the sequence. Did you try putting ORDER BY
jsut before the semicolon at the end?

If [D_C Reference] is a lookup field you could get unexpected results when
sorting, even if the SQL syntax is correct.