Variable Passing not executing in sql

[staspe]

Access 2003

The following work fine as long as the Order By is Hard coded
If the Optional Variable is passed it does not sort correctly.?

Public Sub ExportComboGrainger(strMfrnam As String, strCat As String,
Optional strSort As String)

Dim strSql As String, qd As DAO.QueryDef
Set qd = CurrentDb.QueryDefs("GraingerBrandQuerySql")
If strSort = "" Then

strSql = "SELECT tblCoreSkuInformation.ITEM,
tblCoreSkuInformation.WWGMFRNAME, "
strSql = strSql & "tblCoreSkuInformation.WWGMFRNUM,
tblCoreSkuInformation.WWGDESC, "
strSql = strSql & "tblCoreSkuInformation.RICHTEXT ,
tblCoreSkuInformation.SPIN, "
strSql = strSql & "tblCoreSkuInformation.REDBOOKNUM,
tblCoreSkuInformation.UOM, tblCoreSkuInformation.[UOM Qty], "
strSql = strSql & "tblCoreSkuInformation.[Customer Willcall Qty],
tblCoreSkuInformation.[Customer Ship Qty], "
strSql = strSql & "tblCoreSkuInformation.ALT1, tblSkuCat.[Segment
Name], tblSkuCat.[Category Name]"
strSql = strSql & "FROM tblCoreSkuInformation LEFT JOIN tblSkuCat ON
tblCoreSkuInformation.ITEM = tblSkuCat.[Grainger SKU]"
strSql = strSql & "WHERE (((tblCoreSkuInformation.WWGMFRNAME)='" &
strMfrnam & "') AND ((tblSkuCat.[Category Name])='" & strCat &
"' ))ORDER BY RICHTEXT;"
qd.SQL = strSql
Else
Dim strAdd As Variant
strAdd = "tblCoreSkuInformation."
strAdd = strAdd & strSort
strSql = "SELECT tblCoreSkuInformation.ITEM,
tblCoreSkuInformation.WWGMFRNAME, "
strSql = strSql & "tblCoreSkuInformation.WWGMFRNUM,
tblCoreSkuInformation.WWGDESC, "
strSql = strSql & "tblCoreSkuInformation.RICHTEXT ,
tblCoreSkuInformation.SPIN, "
strSql = strSql & "tblCoreSkuInformation.REDBOOKNUM,
tblCoreSkuInformation.UOM, tblCoreSkuInformation.[UOM Qty], "
strSql = strSql & "tblCoreSkuInformation.[Customer Willcall Qty],
tblCoreSkuInformation.[Customer Ship Qty], "
strSql = strSql & "tblCoreSkuInformation.ALT1, tblSkuCat.[Segment
Name], tblSkuCat.[Category Name]"
strSql = strSql & "FROM tblCoreSkuInformation LEFT JOIN tblSkuCat ON
tblCoreSkuInformation.ITEM = tblSkuCat.[Grainger SKU]"
strSql = strSql & "WHERE (((tblCoreSkuInformation.WWGMFRNAME)= '" &
strMfrnam & "') AND ((tblSkuCat.[Category Name])='" & strCat &
"'))ORDER BY '" & strAdd & "' ;"
qd.SQL = strSql
End If

DoCmd.TransferSpreadsheet acExport, 8, "GraingerBrandQuerySql", "C:
\DM2007\Export_Template.xls", True, ""
qd.Close
Set qd = Nothing
End Sub
I may be wrong But the debug.print is showing the tick ' mark
around the variable..
'tblCoreSkuInformation.ITEM'

When I look at the query in design view... It shows an extra field at
the end , with the 'tblCoreSkuInformation.item'
But It still will not sort...by the variable

When I do not pass the variable. in design view RICHTEXT shows sort
ascending in the field column. not as a seperate column ?

fordraiders

 

[Rick Brandt]

Access 2003

The following work fine as long as the Order By is Hard coded
If the Optional Variable is passed it does not sort correctly.?

Public Sub ExportComboGrainger(strMfrnam As String, strCat As String,
Optional strSort As String)

Dim strSql As String, qd As DAO.QueryDef
Set qd = CurrentDb.QueryDefs("GraingerBrandQuerySql")
If strSort = "" Then

[snip select portion]

"WHERE (((tblCoreSkuInformation.WWGMFRNAME)= '" &
strMfrnam & "') AND ((tblSkuCat.[Category Name])='" & strCat &
"'))ORDER BY '" & strAdd & "' ;"
qd.SQL = strSql
End If

[snip rest]

There should not be single quotes around the variable used in the ORDER BY
clause. You sort on field names not values. Also, shouldn't that variable be
strSort instead if strAdd?