SQL ORDER BY issue

[PatK]

I am creating a SQL SELECT statement in VBA that extracts data from a MS-SQL
DB. All was going well, until I decide to put and "ORDER BY" statement at
the end of the SQL string I was creating. Here is my code snippet:

strTablein = "dbo.hpsc_application"
strFieldin = "Planned_Obs_Date, "
strFieldin = strFieldin & "Actual_Obs_Date, "
strFieldin = strFieldin & "PlannedRetFYQtr, "
strFieldin = strFieldin & "IT_Owner_L2, "
strFieldin = strFieldin & "IT_Owner_L3, "
strFieldin = strFieldin & "IT_Owner_L4, "
strFieldin = strFieldin & "date_of_last_record_update"
strWhere = "IT_Owner_L2 = 'my owner name field (data)'"
strSQL = "SELECT " & strFieldin & " FROM " & strTablein & " WHERE " & _
strWhere & " ORDER BY Actual_Obs_Date DESCENDING"
Debug.Print strSQL
Set rs = con.Execute(strSQL, , 1)

I am getting a sql error on the execute, stating there is an error "near
DESCENDING". I cannot see what that would be. The output of the debug of
strSQL is:

SELECT Planned_Obs_Date, Actual_Obs_Date, PlannedRetFYQtr, IT_Owner_L2,
IT_Owner_L3, IT_Owner_L4, date_of_last_record_update FROM
dbo.hpsc_application WHERE IT_Owner_L2 = 'my owner name field (data)' ORDER
BY Actual_Obs_Date DESCENDING

In the WHERE clause, I am wondering if it is something with the single
quote, or some such. Note, if I remove the "...ORDER BY ....." all the way
to the end of the statement, it works completely fine (just not in the order
I want).

Was hoping another set of eyes might see an obvious error.

Thanks!

PatK

 

[Dick Kusleika]

I am getting a sql error on the execute, stating there is an error "near
DESCENDING". I cannot see what that would be. The output of the debug of
strSQL is:

SELECT Planned_Obs_Date, Actual_Obs_Date, PlannedRetFYQtr, IT_Owner_L2,
IT_Owner_L3, IT_Owner_L4, date_of_last_record_update FROM
dbo.hpsc_application WHERE IT_Owner_L2 = 'my owner name field (data)' ORDER
BY Actual_Obs_Date DESCENDING

I believe the proper syntax is:

ORDER BY Actual_Obs_Date DESC;

 

[PatK]

I shall give that a try. I actually started out with DESC, instead of
DESCENDING, but I have seen both used. Also, I did not have the ";" at the
end, but I saw other threads noting it was option (in fact, my working sql
query select string does not have the ; at the end). Thanks for the response!

patk