Order By With Function Variable

  • Thread starter Thread starter john
  • Start date Start date
J

john

The question is should it work to use a function variable like I do for
WHERE statements, it goes and gets the variable, and the query opens, but is
not sorted.


Function lstSortOrder() As String
lstSortOrder = "BookList.Queue" 'There will be more than one result if
I can get this to work
End Function

Then in my Query for the ORDER BY I write,

ORDER BY lstSortOrder();


Which should return,
ORDER BY BookList.Queue;
 
john said:
The question is should it work to use a function variable like I do
for WHERE statements, it goes and gets the variable, and the query
opens, but is not sorted.


Function lstSortOrder() As String
lstSortOrder = "BookList.Queue" 'There will be more than one
result if I can get this to work
End Function

Then in my Query for the ORDER BY I write,

ORDER BY lstSortOrder();


Which should return,
ORDER BY BookList.Queue;
Click to expand...

The answer is no, it should not work. The name(s) of the field(s) you
want to sort by have to be hard-coded in the SQL statement so that the
statement can be parsed and compiled for execution; they can't be
supplied at run time. In your example, the result is the same as if
your query's SQL was:

ORDER BY "BookList.Queue";

which is not at all the same thing as

ORDER BY BookList.Queue;

To do this sort of thing you have to build the actual SQL statement at
run time, and then put it somewhere to be executed. For example,

Dim strSQL As String

strSQL = _
"SELECT foo, bar FROM BookList " & _
"ORDER BY " & lstSortOrder()

Me.lstMyListbox.RowSource = strSQL
 
Back
Top