How do I sort a listbox by field

[Bruce Rodtnick]

I have a listbox that I've populaed using the row source

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

I have three columns and I would like to be able to sort by the columns. In
other words, sort by Name, then later sort by Email or [Voice Part]. I've
seen a bunch of examples and tried them but nothing seems to work.

Bruce

 

[Jeff Boyce]

Bruce

One approach is to add command controls "above" the listbox and use the
Click event on each to set the Listbox's source to a different SQL statement
(i.e., ORDER BY x -- whatever the command button is the 'label/header' for).

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]

That's what I had in mind...but I'm not addressing it right...I made some
buttons with this code:

Me!lstMailTo.RowSource.OrderBy = Email
Me!lstMailTo.OrderByOn = True
Order By Email

'QuickList.RowSource = TheConstantValue
'Me.QuickList.Requery

Didn't work...

Another :

Dim strSQL As String

'set row source for list box
strSQL = ("[FirstName] & " " & [LastName] AS Name, Personnel.Email,
Personnel.[Voice Part]) _
FROM Personnel
WHERE (((Personnel.Email) Is Not Null) And ((Personnel.Status) = "Active"))
Me.lstMailTo.Recordset OrderBy = "Personnel.LastName",
"Personnel.FirstName";

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

but I'm getting all kinds of errors.

B

Bruce

One approach is to add command controls "above" the listbox and use the
Click event on each to set the Listbox's source to a different SQL
statement (i.e., ORDER BY x -- whatever the command button is the
'label/header' for).

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.

I have a listbox that I've populaed using the row source

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

I have three columns and I would like to be able to sort by the columns.
In other words, sort by Name, then later sort by Email or [Voice Part].
I've seen a bunch of examples and tried them but nothing seems to work.

Bruce

 

[Jeff Boyce]

You provided the code you use to "fill" the listbox, ORDERed BY
[PersonLastName], [PersonFirstName].

Copy that SQL statement, but change the field you use in the ORDER BY clause
to reflect the column on which you wish to sort.

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.

That's what I had in mind...but I'm not addressing it right...I made some
buttons with this code:

Me!lstMailTo.RowSource.OrderBy = Email
Me!lstMailTo.OrderByOn = True
Order By Email

'QuickList.RowSource = TheConstantValue
'Me.QuickList.Requery

Didn't work...

Another :

Dim strSQL As String

'set row source for list box
strSQL = ("[FirstName] & " " & [LastName] AS Name, Personnel.Email,
Personnel.[Voice Part]) _
FROM Personnel
WHERE (((Personnel.Email) Is Not Null) And ((Personnel.Status) =
"Active"))
Me.lstMailTo.Recordset OrderBy = "Personnel.LastName",
"Personnel.FirstName";

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

but I'm getting all kinds of errors.

B

Bruce

One approach is to add command controls "above" the listbox and use the
Click event on each to set the Listbox's source to a different SQL
statement (i.e., ORDER BY x -- whatever the command button is the
'label/header' for).

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.

I have a listbox that I've populaed using the row source

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

I have three columns and I would like to be able to sort by the columns.
In other words, sort by Name, then later sort by Email or [Voice Part].
I've seen a bunch of examples and tried them but nothing seems to work.

Bruce

 

[John W. Vinson]

Me!lstMailTo.RowSource.OrderBy = Email
Me!lstMailTo.OrderByOn = True
Order By Email

A listbox (unlike a Form) does not HAVE an OrderBy property.

Do as Jeff suggested: use your code to change the listbox's RowSource property
to a SQL string which sorts as you want it.

 

[Bruce Rodtnick]

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

B

 

[fredg]

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

B

Name is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

For a more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html

I've taken the liberty to rename your "Name" field to "EmpName".

You also have some quotes and parenthesis in the wrong place.
Try it this way:
The SQL for the list box stays the same for all columns *Except* for
the OrderBy part?
Code each button you use to change the OrderBy like this:

Dim strSQL As String
strSQL = "Select [FirstName] & ' ' & [LastName] AS EmpName,
Personnel.Email, Personnel.[Voice Part] FROM Personnel
WHERE Personnel.Email Is Not Null And Personnel.Status = 'Active' "

Then add to each code the appropriate OrderBy clause.
If this is to order by the Email field add:
strSQL = strSQL & " OrderBy Personnel.Email;"

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

If this is to be used to order by the EmpName column:
strSQL = strSQL & " OrderBy Personnel.[LastName], Personnel.
[FirstName];"

No need to requery.

 

[John W. Vinson]

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.

 

[Bruce Rodtnick]

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

 

[Douglas J. Steele]

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!)

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

 

[Bruce Rodtnick]

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

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!)

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

On Tue, 11 May 2010 14:47:05 -0500, "Bruce Rodtnick"
<[email protected]>
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.

 

[fredg]

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

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!)

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


On Tue, 11 May 2010 14:47:05 -0500, "Bruce Rodtnick"
<[email protected]>
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.

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;"

 

[Bruce Rodtnick]

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

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

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!)

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


On Tue, 11 May 2010 14:47:05 -0500, "Bruce Rodtnick"
<[email protected]>
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.

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;"

 

[Jeff Boyce]

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.

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

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

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!)

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


On Tue, 11 May 2010 14:47:05 -0500, "Bruce Rodtnick"
<[email protected]>
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.

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;"

 

[Douglas J. Steele]

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!)

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

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

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!)

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


On Tue, 11 May 2010 14:47:05 -0500, "Bruce Rodtnick"
<[email protected]>
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.

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;"

 

[Bruce Rodtnick]

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

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.

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

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?

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!)

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


message
On Tue, 11 May 2010 14:47:05 -0500, "Bruce Rodtnick"
<[email protected]>
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;"

 

[Bruce Rodtnick]

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

B

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!)

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

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?

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!)

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


message
On Tue, 11 May 2010 14:47:05 -0500, "Bruce Rodtnick"
<[email protected]>
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;"

 

[Jeff Boyce]

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.

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

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.

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
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?

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!)

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


message
On Tue, 11 May 2010 14:47:05 -0500, "Bruce Rodtnick"
<[email protected]>
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;"