A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How do I sort a listbox by field



 
 
Thread Tools Display Modes
  #11  
Old May 12th, 2010, 05:07 PM posted to microsoft.public.access.forms
Bruce Rodtnick
external usenet poster
 
Posts: 16
Default 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]







  #12  
Old May 12th, 2010, 05:37 PM posted to microsoft.public.access.forms
fredg
external usenet poster
 
Posts: 4,386
Default How do I sort a listbox by field

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
  #13  
Old May 12th, 2010, 06:14 PM posted to microsoft.public.access.forms
Bruce Rodtnick
external usenet poster
 
Posts: 16
Default How do I sort a listbox by field

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



  #14  
Old May 12th, 2010, 07:18 PM posted to microsoft.public.access.forms
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default How do I sort a listbox by field

Bruce

You may wish to spend some time using Access HELP to verify your syntax.

Another approach would be to first create a query that does what you want in
the query design view, then switch to the SQL view to see/get the SQL code
that works.

When I look at your expression, I wonder why you have a comma before the
ORDER BY clause?

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"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





  #15  
Old May 12th, 2010, 08:40 PM posted to microsoft.public.access.forms
Douglas J. Steele[_3_]
external usenet poster
 
Posts: 3,143
Default How do I sort a listbox by field

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





  #16  
Old May 12th, 2010, 08:45 PM posted to microsoft.public.access.forms
Bruce Rodtnick
external usenet poster
 
Posts: 16
Default How do I sort a listbox by field

Yes, I've tried taking the SQL code and pasting it in my code....but I keep
getting a compile error: Sub or Function not Defined on the word FROM. The
Help there doesn't really tell me much. This is my entire code:

Private Sub lblVoicePart_Click()
Dim strSQL As String
strSQL = "SELECT [FirstName] & ' ' & [LastName] AS MbrName, Personnel.Email,
Personnel.[Voice Part], Personnel.LastName, Personnel.FirstName"
FROM [Personnel]
WHERE (((Personnel.Email) Is Not Null) And ((Personnel.Status) = "active"))


'If this is to order by the [Voice Part] field, then use:
strSQL = strSQL & " OrderBy Personnel.[Voice Part];"

End Sub

B

"Jeff Boyce" wrote in message
...
Bruce

You may wish to spend some time using Access HELP to verify your syntax.

Another approach would be to first create a query that does what you want
in the query design view, then switch to the SQL view to see/get the SQL
code that works.

When I look at your expression, I wonder why you have a comma before the
ORDER BY clause?

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"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







  #17  
Old May 12th, 2010, 08:48 PM posted to microsoft.public.access.forms
Bruce Rodtnick
external usenet poster
 
Posts: 16
Default How do I sort a listbox by field

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







  #18  
Old May 13th, 2010, 07:37 PM posted to microsoft.public.access.forms
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default How do I sort a listbox by field

Please verify your syntax ...

I believe "ORDER BY" needs a space...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.


"Bruce Rodtnick" wrote in message
...
Yes, I've tried taking the SQL code and pasting it in my code....but I
keep getting a compile error: Sub or Function not Defined on the word
FROM. The Help there doesn't really tell me much. This is my entire
code:

Private Sub lblVoicePart_Click()
Dim strSQL As String
strSQL = "SELECT [FirstName] & ' ' & [LastName] AS MbrName,
Personnel.Email, Personnel.[Voice Part], Personnel.LastName,
Personnel.FirstName"
FROM [Personnel]
WHERE (((Personnel.Email) Is Not Null) And ((Personnel.Status) =
"active"))


'If this is to order by the [Voice Part] field, then use:
strSQL = strSQL & " OrderBy Personnel.[Voice Part];"

End Sub

B

"Jeff Boyce" wrote in message
...
Bruce

You may wish to spend some time using Access HELP to verify your syntax.

Another approach would be to first create a query that does what you want
in the query design view, then switch to the SQL view to see/get the SQL
code that works.

When I look at your expression, I wonder why you have a comma before the
ORDER BY clause?

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"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








 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:44 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.