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]