Dynamic sorting of listbox based on crosstab

[Robert]

What I'm trying to do is sort a listbox by column when the user clicks a
button. The list box is based on a crosstab query. The problem is whenever
I add an ORDER BY clause to the query it fizzles out:

U1 = "TRANSFORM First(qryListing.word) AS FirstOfword "
U1 = U1 & "SELECT qryListing.wordid FROM qryListing GROUP BY
qryListing.wordid "
U1 = U1 & "PIVOT qryListing.abbr"
U1 = U1 & " ORDER BY qryListing_Crosstab.Esp "
Me.List0.RowSource = U1

doesn't work. The listbox blanks out. But

U1 = "TRANSFORM First(qryListing.word) AS FirstOfword "
U1 = U1 & "SELECT qryListing.wordid FROM qryListing GROUP BY
qryListing.wordid "
U1 = U1 & "PIVOT qryListing.abbr"
'U1 = U1 & " ORDER BY qryListing_Crosstab.Esp "
Me.List0.RowSource = U1


works (note the commented-out ORDER BY clause) (but doesn't produce the
desired sort). " ORDER BY qryListing_Crosstab.Esp " is taken from the
recordsource of the listbox when I specify a sort there. And it works
coming from there. But I need to be able to allow the user to change the
sort column on the form.

Is there something wrong with this code or the way I'm setting it? Or does
anybody know how to sort by selected column in a listbox when the listbox is
based on a crosstab?

Robert

 

[Mark A. Sam]

Robert,

I suggest, if you haven't done this, that you create the Rowsource property
sql from the query builder first. When you have it working, then copy the
sql and build your U1 string from that. And repeat this for each different
value of U1. If it works in the query builder, it will work in your code.

God Bless,

Mark A. Sam

 

[Robert]

I did that and it works. thanks.

Robert,

I suggest, if you haven't done this, that you create the Rowsource
property sql from the query builder first. When you have it working, then
copy the sql and build your U1 string from that. And repeat this for each
different value of U1. If it works in the query builder, it will work in
your code.

God Bless,

Mark A. Sam