what is wrong with my sql statment?

[Al]

I am getting "Syntax error in the order by clause" on the following sql
statment. the statment is in the code behind a button. the sql works without
a problem if I take out the Order By.

ssql = "SELECT RevCommID, RevCommentDate, ProjCode, Proj_Name,
ProjectManager, "
ssql = ssql & "SubmittalPhase, PercentComplete, SheetNumber,
Grid_KeyWord_DetailNo, "
ssql = ssql & "ReviewCategory, Reviewer, RevComment, Attachment,
AEResponder, "
ssql = ssql & "RTC, AEResponse, AERespCost, AEValidation "
ssql = ssql & "INTO " & Me.txtTableName
ssql = ssql & " FROM qryExportComments "
ssql = ssql & "GROUP BY RevCommID, RevCommentDate, ProjCode, Proj_Name,
ProjectManager, "
ssql = ssql & "SubmittalPhase, PercentComplete, SheetNumber,
Grid_KeyWord_DetailNo, "
ssql = ssql & "ReviewCategory, Reviewer, RevComment, Attachment,
AEResponder, "
ssql = ssql & "RTC, AEResponse, AERespCost, AEValidation "
ssql = ssql & "ORDER BY SheetNumber, Reviewer "

 

[Jeanette Cunningham]

Al,
in the order by clause, either specify the fields with the syntax -
Tablename.Fieldname
or use the column numbers instead.
Example to order a query by the second and third columns you put
order by 2, 3


Jeanette Cunningham -- Melbourne Victoria Australia

 

[Al]

did that:
ORDER BY qryExportComments.SheetNumber, qryExportComments.Reviewer
still getting the error

 

[Jeanette Cunningham]

Al,
the order by clause probably is causing a conflicting sort order with the
constraints of the Group By clause you have.
You can use a Having clause instead of Order by with a Group By query.
You can try to sort it out by copying and pasting the sql into the query
designer to help you sort it out.
In your ssql, comment out the order by clause.
Put a Debug.Print line to get the ssql in a form which will copy and paste
into the query designer.
At the end of the ssql put:

Debug.Print ssql

Run the form, go Ctl + G to open the immediate window, copy and paste into
the sql view of a new query.
Try to sort it out in the query designer, then switch to sql view to see how
it is written.
Make the amendments to your code.


Jeanette Cunningham -- Melbourne Victoria Australia

 

[Al]

Thank you Jeanette very much this helped me a lot to eliminate the problem
that I had and get it to work the way I want. Thanks for your help
Al