HA!!!!! That did it! Thanx!

B


"Douglas J. Steele" wrote in message
...
Where did the comma come from?

For the original problem, try

Me!lstMailTo.RowSource = Replace(strSQL, ";", " ")

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

"Bruce Rodtnick" wrote in message
...
Sorry, I was responding to Doug Steele's post. I tried your code and
nothing changed. This is the strSQL it created:

"Select [FirstName] & ' ' & [LastName] AS mbrName, Personnel.Email,
Personnel.[Voice Part], FROM Personnel, WHERE (((Personnel.Email) Is Not
Null) And ((Personnel.Status) = 'Active')), OrderBy Personnel.[Voice
Part];"

I added the parentheses, but nothing happened.

B
"fredg" wrote in message
...
On Wed, 12 May 2010 11:07:15 -0500, Bruce Rodtnick wrote:

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]





Did you not read my reply?
You get "rid" of that first semicolon by not putting it in in the
first place.

strSQL = "Select blah blah"
strSQL = strSQL & " Order by Personnel.email;"


--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail