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

On Thu, 24 Jul 2008 17:13:00 -0700, PatK
wrote:

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;
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

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

"Dick Kusleika" wrote:

On Thu, 24 Jul 2008 17:13:00 -0700, PatK
wrote:

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;
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com