How do I sort a listbox by field

But with this code...HOW do I get rid of the semi-Colon?


"Douglas J. Steele" wrote in message
...
Yes. Get rid of the semi-colon (and make sure there's a space between the
closing parenthesis and the key word Order)

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"Bruce Rodtnick" wrote in message
...
This all make sence...but I tried it and I get a blank screen. The
strSQL shows that everything is right.

"SELECT [FirstName] & " " & [LastName] AS MbrName, Personnel.Email,
Personnel.[Voice Part]
FROM Personnel
WHERE (((Personnel.Email) Is Not Null) AND
((Personnel.Status)="active"));
ORDER BY Personnel.Email"

Is it the semi-colon before the ORDER BY that does it? How do I get rid
of that?

B


"John W. Vinson" wrote in message
...
On Tue, 11 May 2010 14:47:05 -0500, "Bruce Rodtnick"

wrote:

And that's what I'm TRYING to do...This is what I have now:

Dim strSQL As String

strSQL = Me.lstMailTo.RowSource

strSQL = strSQL & " " & ORDER By Personnel.Email

Me!lstMailTo.RowSource = strSQL
Me!lstMailTo.Requery

***********

strSQL is picking up the proper RowSource but when I try to add the
ORDER BY
to the strSQL I'm getting a two ORDER BYs in my strSQL...the old one and
the
new one...and so I get NOTHING in the ListBox lstMailTo

The problem is that strSQL is *the whole thing* - it isn't a complex
object
with an Order By property that you can replace; it's just a text string.
VBA
won't have a clue what you mean by the ORDER By Personnel.Email text
there;
that's SQL text, not valid VBA code.

I'd suggest using a saved query in your table, without *any* ORDER BY
clause,
and use code like

strSQL = Currentdb.QueryDefs("lstMailToTemplateQuery").SQL
strSQL = strSQL & " ORDER BY Personnel.Email"
Me!lstMailTo.RowSource = strSQL

You won't need to requery it - setting its rowsource does the job.
--

John W. Vinson [MVP]