Can a concantenated string be sorted?

[Guest]

I have a listbox built on a query. The listbox resorts when the user clicks a command button placed at the top of each column. (e.g."Date", "City", "State") I had a concantenated name ("first"&" "&"last") and the sort routine did not work. Once I rebuilt the listbox to have separate firstname and lastname columns and command buttons, then the routine worked.

I can't imagine that Access can't sort a string, so I must have something wrong somewhere..

Oh, btw if you're wondering "What's wrong with separate columns?" - it's just for appearances, I don't like the line separating the two names..

Rick...

 

[MacDermott]

I would expect a list sorted on a concatenated field to sort like this:
Bob Jones
Bob Yates
Randy Jones

One sorted on Last Name then First Name would return this:
Bob Jones
Randy Jones
Bob Yates

Is that what you're experiencing?
("The routine did not work" doesn't give a lot to go on.)

HTH
- Turtle

I have a listbox built on a query. The listbox resorts when the user

clicks a command button placed at the top of each column. (e.g."Date",
"City", "State") I had a concantenated name ("first"&" "&"last") and the
sort routine did not work. Once I rebuilt the listbox to have separate
firstname and lastname columns and command buttons, then the routine worked.

I can't imagine that Access can't sort a string, so I must have something wrong somewhere...

Oh, btw if you're wondering "What's wrong with separate columns?" - it's

just for appearances, I don't like the line separating the two names...

 

[Guest]

Hi MacDermott

Thank you for the quick response. And sorry for the lack of clarity

I'm using a downloaded function (written by Dieter Liessman at www.topsolutions.de) called "LFSort" - Here's the code

'will amend the ORDER BY-part of a listbox's RowSource in order to have it sorted b
'the column(s) specified in strOrderField
Public Function LFSort(strRowSource As String, strOrderFields As String) As Strin

Dim intWhere As Intege
Dim intUpDown As Strin

If IsNull(strRowSource) Then GoTo end

intWhere = InStr(strRowSource, "DESC"
If intWhere <> 0 The
intUpDown = ";
Els
intUpDown = " DESC;
End I
intWhere = InStr(strRowSource, "Order By"
strRowSource = Left$(strRowSource, intWhere + 8
LFSort = strRowSource & strOrderFields & intUpDow

ende
End Functio

The event procedure looks like this

Private Sub cmdM1_Click(
'use Dieter's function to get a new ORDER BY-string / sort accordingl
lstBox.RowSource = LFSort(lstBox.RowSource, "M1"

'focus back on listbo
lstBox.SetFocu
End Su

"The routine did not work" means: When the command button for 'name' is clicked, the listbox re-sorts to the AutoNumberID of the records and not the alphabetical sort of the concantenated 'name' string I was expecting

For the 'name' column in the query, I had Installer:InstallerFirstName&" "&InstallerLastNam
For the event procedure, I had: (lstBox.RowSource, "Installer"

Now that I'm writing this down, it occurs to me to have InstallerFirstName and InstallerLastName in the query and set the listbox column width properties for those two columns to 0". That way the concantenated string is shown, and the function could use the appropriate rowsource (InstallerFirstName or InstallerLastName). That would solve my aesthetics problem, but not the question I posted..

----- MacDermott wrote: ----

I would expect a list sorted on a concatenated field to sort like this
Bob Jone
Bob Yate
Randy Jone

One sorted on Last Name then First Name would return this
Bob Jone
Randy Jone
Bob Yate

Is that what you're experiencing
("The routine did not work" doesn't give a lot to go on.

HT
- Turtl

I have a listbox built on a query. The listbox resorts when the use

clicks a command button placed at the top of each column. (e.g."Date"
"City", "State") I had a concantenated name ("first"&" "&"last") and th
sort routine did not work. Once I rebuilt the listbox to have separat
firstname and lastname columns and command buttons, then the routine worked

 

[MacDermott]

Yes, that gives me a much better idea of what's going on.

However, I'm quite unsure why you're passing this function the second
argument of "M1".
Is "M1" the name of a field in your query?

- Turtle

Hi MacDermott,

Thank you for the quick response. And sorry for the lack of clarity.

I'm using a downloaded function (written by Dieter Liessman at

www.topsolutions.de) called "LFSort" - Here's the code:

'will amend the ORDER BY-part of a listbox's RowSource in order to have it sorted by
'the column(s) specified in strOrderFields
Public Function LFSort(strRowSource As String, strOrderFields As String) As String

Dim intWhere As Integer
Dim intUpDown As String

If IsNull(strRowSource) Then GoTo ende

intWhere = InStr(strRowSource, "DESC")
If intWhere <> 0 Then
intUpDown = ";"
Else
intUpDown = " DESC;"
End If
intWhere = InStr(strRowSource, "Order By")
strRowSource = Left$(strRowSource, intWhere + 8)
LFSort = strRowSource & strOrderFields & intUpDown

ende:
End Function

The event procedure looks like this:

Private Sub cmdM1_Click()
'use Dieter's function to get a new ORDER BY-string / sort accordingly
lstBox.RowSource = LFSort(lstBox.RowSource, "M1")

'focus back on listbox
lstBox.SetFocus
End Sub

"The routine did not work" means: When the command button for 'name' is

clicked, the listbox re-sorts to the AutoNumberID of the records and not the
alphabetical sort of the concantenated 'name' string I was expecting.

For the 'name' column in the query, I had Installer:InstallerFirstName&" "&InstallerLastName
For the event procedure, I had: (lstBox.RowSource, "Installer")

Now that I'm writing this down, it occurs to me to have InstallerFirstName

and InstallerLastName in the query and set the listbox column width
properties for those two columns to 0". That way the concantenated string
is shown, and the function could use the appropriate rowsource
(InstallerFirstName or InstallerLastName). That would solve my aesthetics
problem, but not the question I posted...

 

[Guest]

Hi MacDermott

(For some reason the subject and add original did not display when I clicked reply.... I hope this is posted in the right thread.

Anyway, "M1" is from a sample (not my actual) - it is both the name of the command button and the field name of the table that the listbox sample is based upon

For my application, I was careful to change the event procedure code to match the name of the command button and the field name appropriately. So when the TimeClockDate button was clicked, the listbox sorted to the TimeClockDate field, when the PunchIn button was clicked, the listbox sorted to the PunchIn field.

I only encountered a problem when I referred the event code to a field in the query that concantenates two fields from a table into one field on the query. (From the tblInstallers, given an alias in the query as 'Installer'. The query field is: Installer:InstallerFirstName&" "&InstallerLastName . The listbox is populates correctly with a concantenated name.

So my thought is that either a) I'm doing something wrong or b) the concantenated string can't be sorted. It doesn't seem likely that 'b' would be the case so - what am I doing wrong

Rick...

 

[MacDermott]

So what you actually have is a procedure that looks like this?

Private Sub cmdInstaller_Click()
'use Dieter's function to get a new ORDER BY-string / sort
accordingly
lstBox.RowSource = LFSort(lstBox.RowSource, "Installer")

'focus back on listbox
lstBox.SetFocus
End Sub

Hi MacDermott,

(For some reason the subject and add original did not display when I

clicked reply.... I hope this is posted in the right thread.)

Anyway, "M1" is from a sample (not my actual) - it is both the name of the

command button and the field name of the table that the listbox sample is
based upon.

For my application, I was careful to change the event procedure code to

match the name of the command button and the field name appropriately. So
when the TimeClockDate button was clicked, the listbox sorted to the
TimeClockDate field, when the PunchIn button was clicked, the listbox sorted
to the PunchIn field.

I only encountered a problem when I referred the event code to a field in

the query that concantenates two fields from a table into one field on the
query. (From the tblInstallers, given an alias in the query as 'Installer'.
The query field is: Installer:InstallerFirstName&" "&InstallerLastName .
The listbox is populates correctly with a concantenated name.)

So my thought is that either a) I'm doing something wrong or b) the

concantenated string can't be sorted. It doesn't seem likely that 'b' would
be the case so - what am I doing wrong?

 

[Guest]

MacDermott

Yes that's correct. Now that you've written it down, I feel stupid for not giving you the right information in the first place - sorry. :-(

So, why does this not work? (And thank you for taking the time to look at this.

Rick..

----- MacDermott wrote: ----

So what you actually have is a procedure that looks like this

Private Sub cmdInstaller_Click(
'use Dieter's function to get a new ORDER BY-string / sor
accordingl
lstBox.RowSource = LFSort(lstBox.RowSource, "Installer"

'focus back on listbo
lstBox.SetFocu
End Su

Hi MacDermott

command button and the field name of the table that the listbox sample i
based uponmatch the name of the command button and the field name appropriately. S
when the TimeClockDate button was clicked, the listbox sorted to th
TimeClockDate field, when the PunchIn button was clicked, the listbox sorte
to the PunchIn fieldthe query that concantenates two fields from a table into one field on th
query. (From the tblInstallers, given an alias in the query as 'Installer'
The query field is: Installer:InstallerFirstName&" "&InstallerLastName
The listbox is populates correctly with a concantenated name.concantenated string can't be sorted. It doesn't seem likely that 'b' woul
be the case so - what am I doing wrong

 

[rkc]

MacDermott,

Yes that's correct. Now that you've written it down, I feel stupid for

not giving you the right information in the first place - sorry. :-(

So, why does this not work? (And thank you for taking the time to look at

this.)

Say you wrote a test routine, something like the following and put the
cursor at the
beggining of the routine and hit F5. What would you expect to see printed in
the
debug window [ctrl-G]?

Sub TestLFSort()
Dim s As String
s = "SELECT * FROM Table1 WHERE x = y ORDER BY Y DESC"
Debug.Print LFSort(s, "MONKEYS")
End Sub

 

[MacDermott]

Just making sure we're on the same page.
Sorry if I've made you feel foolish -
that's a more difficult state to learn in.

I did this:
I created a query something like this:
SELECT [FieldA] & [FieldB] AS MyField FROM MyTable

Then in Design View, I added Ascending to the Sort under MyField.
The SQL I got looked like this:
SELECT [FieldA] & [FieldB] AS MyField FROM MyTable ORDER BY [FieldA] &
[FieldB] .

Note that it did not say "ORDER BY MyField".

Based on this, I would recommend something like this:
lstBox.RowSource = LFSort(lstBox.RowSource, "InstallerFirstName & ' ' &
InstallerLastName ")

This would create a sort on your Installer field, starting with the first
name.
Personally, I'd rather sort by last name first (maybe I'm just old-fashioned
that way), so I'd tend to use this:
lstBox.RowSource = LFSort(lstBox.RowSource, "InstallerLastName ,
InstallerFirstName ")

HTH
- Turtle

MacDermott,
lstBox.RowSource = LFSort(lstBox.RowSource, "Installer")

Yes that's correct. Now that you've written it down, I feel stupid for

not giving you the right information in the first place - sorry. :-(

 

[Guest]

Hello MacDermott

No need to apologize - it's how I learn, by making mistakes

I created a new form with only a listbox on it and I set it up per your suggestion (for testing purposes). At first it wasn't working, then I figured out that I hadn't added the ORDER BY to the SQL. Once I did that, then the sorts worked. However, Access puts up a parameter box asking for "Installer.InstallerFirstName" upon opening the form and I don't understand why. Here's the SQL

SELECT Installer.InstallerID & " " & Installer.InstallerFirstName & " " & Installer.InstallerLastName AS Installer FROM tblInstallers AS Installer ORDER BY Installer.InstallerLastName , Installer.InstallerFirstName

Here's the setup I have now (and it is not asking for a parameter). The Listbox has 4 columns and the first 3 are formated to 0". Here's the SQL on the rowsource

SELECT Installer.InstallerID, Installer.InstallerFirstName, Installer.InstallerLastName, Installer.InstallerID & " " & Installer.InstallerFirstName & " " & Installer.InstallerLastName AS Installer FROM tblInstallers AS Installer ORDER BY Installer.InstallerID DESC

Here's the VBA code for the form

Option Compare Databas
Option Explici
Private Sub cmdInstallerFirstName_Click(
lboInstallerList.RowSource = LFSort(lboInstallerList.RowSource, "Installer.InstallerFirstName"
lboInstallerList.SetFocu
End Su
Private Sub cmdInstallerLastName_Click(
lboInstallerList.RowSource = LFSort(lboInstallerList.RowSource, "Installer.InstallerLastName"
lboInstallerList.SetFocu
End Su
Private Sub cmdInstallerLastThenFirstName_Click(
lboInstallerList.RowSource = LFSort(lboInstallerList.RowSource, "Installer.InstallerLastName , Installer.InstallerFirstName "
lboInstallerList.SetFocu
End Su
Private Sub cmdInstallerID_Click(
lboInstallerList.RowSource = LFSort(lboInstallerList.RowSource, "Installer.InstallerID"
lboInstallerList.SetFocu
End Su

The various sort options allow the user to view the way they want. Clicking the "Last Name" button results in
#123 Bob Murra
#124 Bill Murray
Clicking the "Last then First" button results in
#124 Bill Murray
#123 Bob Murra

By the way, I had been wondering how to sort by more than one field and your suggestion answered that question before I even asked it. Thanks

Rick..
----- MacDermott wrote: ----

Just making sure we're on the same page
Sorry if I've made you feel foolish
that's a more difficult state to learn in

I did this
I created a query something like this
SELECT [FieldA] & [FieldB] AS MyField FROM MyTabl

Then in Design View, I added Ascending to the Sort under MyField
The SQL I got looked like this
SELECT [FieldA] & [FieldB] AS MyField FROM MyTable ORDER BY [FieldA]
[FieldB]

Note that it did not say "ORDER BY MyField"

Based on this, I would recommend something like this
lstBox.RowSource = LFSort(lstBox.RowSource, "InstallerFirstName & ' '
InstallerLastName "

This would create a sort on your Installer field, starting with the firs
name
Personally, I'd rather sort by last name first (maybe I'm just old-fashione
that way), so I'd tend to use this
lstBox.RowSource = LFSort(lstBox.RowSource, "InstallerLastName
InstallerFirstName "

HT
- Turtl

 

[Guest]

Hello rkc

Just so that you know, I am new to Access (this is my first relational database). I have done alot of work with Excel using formulas so I can 'sort of' follow some of the VBA code. So far, I haven't used the debug window or the immediate window and when I first read your post, I thought they were different windows!

To answer your question - I don't know for certain. But I suspect that it would print a list (from table1) that was sorted by the field name 'MONKEYS'. The "x" and "y" references throw me - I can only guess what they are in the code for

I'm going to guess at the 'English' translation of your test

In the first line, 's' is declared as a string
In the second line, a statement that 's' is equal to the SQL and the SQL has a variable ORDER BY based upon the WHERE x
In the third line, the LFSort function is called using the 's' string and making "MONKEYS" as the 'x' variable

Rick..

----- rkc wrote: ----

MacDermott

this.

Say you wrote a test routine, something like the following and put th
cursor at th
beggining of the routine and hit F5. What would you expect to see printed i
th
debug window [ctrl-G]

Sub TestLFSort(
Dim s As Strin
s = "SELECT * FROM Table1 WHERE x = y ORDER BY Y DESC
Debug.Print LFSort(s, "MONKEYS"
End Su

 

[rkc]

To answer your question - I don't know for certain. But I suspect that it

would print a list (from table1) that was sorted by the field name
'MONKEYS'. The "x" and "y" references throw me - I can only guess what they
are in the code for.

I asked for two reasons. First to see if you had tried to understand the
function before
you plugged it into your application. There's nothing wrong with borrowing
code, that's
why people make it available. It's just a good idea to figure out what it
does before you
rely on it. As it was posted the LFSort function only half works. It doesn't
handle the
parsing of the DESC portion of an ORDER BY clause correctly.
Second was to point out a simple way to write and run a test of the function
outside
the code of your application. You could have taken the actual sql string
used as the
record source of your form, fed it to the LFSort function using different
strOrderFields
parameters and checked the output for a valid sql string. The easiest way to
check the
validity of an sql string is to take it and paste it into the SQL view of
the query builder.
It it works and returns the result you expected, you're good to go.

 

[MacDermott]

I'm glad it's working for you.
Looking at both of the SQL statements you posted, I notice that you have
both one field (the combined name field) and the table aliased as Installer.
While I guess the good news is that it's working, I'd suggest using
different aliases. Perhaps the combined name field could be InstallerName.

As for the parameter box, I'd have to guess that there was a misspelling
somewhere - perhaps an Instaler or some such?

HTH
- Turtle

Hello MacDermott,

No need to apologize - it's how I learn, by making mistakes.

I created a new form with only a listbox on it and I set it up per your

suggestion (for testing purposes). At first it wasn't working, then I
figured out that I hadn't added the ORDER BY to the SQL. Once I did that,
then the sorts worked. However, Access puts up a parameter box asking for
"Installer.InstallerFirstName" upon opening the form and I don't understand
why. Here's the SQL:

SELECT Installer.InstallerID & " " & Installer.InstallerFirstName & " " &

Installer.InstallerLastName AS Installer FROM tblInstallers AS Installer
ORDER BY Installer.InstallerLastName , Installer.InstallerFirstName ;

Here's the setup I have now (and it is not asking for a parameter). The

Listbox has 4 columns and the first 3 are formated to 0". Here's the SQL on
the rowsource:

SELECT Installer.InstallerID, Installer.InstallerFirstName,

Installer.InstallerLastName, Installer.InstallerID & " " &
Installer.InstallerFirstName & " " & Installer.InstallerLastName AS
Installer FROM tblInstallers AS Installer ORDER BY Installer.InstallerID
DESC;

Here's the VBA code for the form:

Option Compare Database
Option Explicit
Private Sub cmdInstallerFirstName_Click()
lboInstallerList.RowSource = LFSort(lboInstallerList.RowSource, "Installer.InstallerFirstName")
lboInstallerList.SetFocus
End Sub
Private Sub cmdInstallerLastName_Click()
lboInstallerList.RowSource = LFSort(lboInstallerList.RowSource, "Installer.InstallerLastName")
lboInstallerList.SetFocus
End Sub
Private Sub cmdInstallerLastThenFirstName_Click()
lboInstallerList.RowSource = LFSort(lboInstallerList.RowSource,

"Installer.InstallerLastName , Installer.InstallerFirstName ")

lboInstallerList.SetFocus
End Sub
Private Sub cmdInstallerID_Click()
lboInstallerList.RowSource = LFSort(lboInstallerList.RowSource, "Installer.InstallerID")
lboInstallerList.SetFocus
End Sub

The various sort options allow the user to view the way they want.

Clicking the "Last Name" button results in:

#123 Bob Murray
#124 Bill Murray
Clicking the "Last then First" button results in:
#124 Bill Murray
#123 Bob Murray

By the way, I had been wondering how to sort by more than one field and

your suggestion answered that question before I even asked it. Thanks!

Rick...
----- MacDermott wrote: -----

Just making sure we're on the same page.
Sorry if I've made you feel foolish -
that's a more difficult state to learn in.

I did this:
I created a query something like this:
SELECT [FieldA] & [FieldB] AS MyField FROM MyTable

Then in Design View, I added Ascending to the Sort under MyField.
The SQL I got looked like this:
SELECT [FieldA] & [FieldB] AS MyField FROM MyTable ORDER BY [FieldA] &
[FieldB] .

Note that it did not say "ORDER BY MyField".

Based on this, I would recommend something like this:
lstBox.RowSource = LFSort(lstBox.RowSource, "InstallerFirstName & ' ' &
InstallerLastName ")

This would create a sort on your Installer field, starting with the first
name.
Personally, I'd rather sort by last name first (maybe I'm just old-fashioned
that way), so I'd tend to use this:
lstBox.RowSource = LFSort(lstBox.RowSource, "InstallerLastName ,
InstallerFirstName ")

HTH
- Turtle

 

[Guest]

Hi rkc

Comments in-line..

----- rkc wrote: ----

As it was posted the LFSort function only half works. It doesn'
handle the parsing of the DESC portion of an ORDER BY clause correctly

OK, I'm going dense here... I don't understand "parsing of the DESC portion... ". If the LFSort function doesn't do that correctly, then why does the it work in my application? When I look at the SQL, in-between "ORDER BY" and "DESC" is the field name of the last selected sort variable. Maybe the "missing" portion of the function is filled in by the event procedure? (All of the details of the SQL and the event procedures are in my response to MacDermott.

Second was to point out a simple way to write and run a test of the functio
outside the code of your application. You could have taken the actual sql strin
used as the record source of your form, fed it to the LFSort function using differen
strOrderFields parameters and checked the output for a valid sql string.

The easiest way to check the validity of an sql string is to take it and paste it into the SQL view o
the query builder. It it works and returns the result you expected, you're good to go

So I'm discovering. I've been doing some experimenting with the SQL and not always getting the syntax right, let alone the results. But, at least by making these mistakes I am finding out what doesn't work and what does..

 

[rkc]

Hi rkc,

Comments in-line...

----- rkc wrote: -----

As it was posted the LFSort function only half works. It doesn't
handle the parsing of the DESC portion of an ORDER BY clause correctly.

OK, I'm going dense here... I don't understand "parsing of the DESC

portion... ".

The LFSort function (as posted) not only changes the fields the
query is sorted on it changes the sort order. That's not mentioned in the
comment that explains the purpose of the function. Is it what you expected?

The function call:
LFSort ("SELECT * FROM tblInstallers ORDER BY tblInstallers.InstallerID
DESC;", _
"tblInstallers.InstallerFirstName")

Returns:
SELECT * FROM tblInstallers ORDER BY tblInstallers.InstallerFirstName;

The DESC part is dropped.

 

[Guest]

Hello rkc

Thank you for going through this with me - it is helping me to gain a little more understanding. I have put comments in-line

----- rkc wrote: ----


The LFSort function (as posted) not only changes the fields th
query is sorted on it changes the sort order. That's not mentioned in th
comment that explains the purpose of the function. Is it what you expected

I "understood" it (before I really knew how to read the code) that the sort order would alternately change between ascending and descending - that is what it does and that happens to work out fine with my current application

The function call
LFSort ("SELECT * FROM tblInstallers ORDER BY tblInstallers.InstallerI
DESC;",
"tblInstallers.InstallerFirstName"

Returns
SELECT * FROM tblInstallers ORDER BY tblInstallers.InstallerFirstName

The DESC part is dropped

Now that you are placing focus on the word DESC... On your first post with that word, I was thinking that is was an abbreviation for DESCription and when I looked at the SQL of my app, there it was on the end of the line. Today when I read your post I tried reviewing the SQL after every click event and I can see that it really is an abbreviation for DESCending. So when the function uses the "intUpDown", the function does it's read of the current SQL and if it finds a DESC there it removes it and places a ";". If it doesn't it places a "DESC;". Am I interpreting this correctly? (Also, you had given me a test on your first post. How far off was my answer?

Thanks again for your participation on this thread

Rick..

 

[Guest]

Hello MacDermott

Thank you for taking the time to look at this. I like your suggestion for the combined field name and will use it

On the parameter window it was asking for "IInstallerFirstName". I initially thought that I had a mispelling, so I inspected the query and the SQL and did not locate the double capital "I". Chances are that I just overlooked it. I'll try to remember that a mispelling can cause the parameter box to pop-up should I ever get an unexpected pop-up

Thanks again for all of the time you put into this. I really appreciate it

Rick..

----- MacDermott wrote: ----

I'm glad it's working for you
Looking at both of the SQL statements you posted, I notice that you hav
both one field (the combined name field) and the table aliased as Installer
While I guess the good news is that it's working, I'd suggest usin
different aliases. Perhaps the combined name field could be InstallerName

As for the parameter box, I'd have to guess that there was a misspellin
somewhere - perhaps an Instaler or some such

HT
- Turtl

Hello MacDermott

suggestion (for testing purposes). At first it wasn't working, then
figured out that I hadn't added the ORDER BY to the SQL. Once I did that
then the sorts worked. However, Access puts up a parameter box asking fo
"Installer.InstallerFirstName" upon opening the form and I don't understan
why. Here's the SQLInstaller.InstallerLastName AS Installer FROM tblInstallers AS Installe
ORDER BY Installer.InstallerLastName , Installer.InstallerFirstNameListbox has 4 columns and the first 3 are formated to 0". Here's the SQL o
the rowsourceInstaller.InstallerLastName, Installer.InstallerID & " "
Installer.InstallerFirstName & " " & Installer.InstallerLastName A
Installer FROM tblInstallers AS Installer ORDER BY Installer.InstallerI
DESC

Option Explici
Private Sub cmdInstallerFirstName_Click(
lboInstallerList.RowSource = LFSort(lboInstallerList.RowSource "Installer.InstallerFirstName"
lboInstallerList.SetFocu
End Su
Private Sub cmdInstallerLastName_Click(
lboInstallerList.RowSource = LFSort(lboInstallerList.RowSource "Installer.InstallerLastName"
lboInstallerList.SetFocu
End Su
Private Sub cmdInstallerLastThenFirstName_Click(
lboInstallerList.RowSource = LFSort(lboInstallerList.RowSource

"Installer.InstallerLastName , Installer.InstallerFirstName "

lboInstallerList.SetFocu
End Su
Private Sub cmdInstallerID_Click(
lboInstallerList.RowSource = LFSort(lboInstallerList.RowSource "Installer.InstallerID"
lboInstallerList.SetFocu
End Su

Clicking the "Last Name" button results in

#123 Bob Murra
#124 Bill Murra
Clicking the "Last then First" button results in
#124 Bill Murra
#123 Bob Murra

By the way, I had been wondering how to sort by more than one field an your suggestion answered that question before I even asked it. Thanks
Rick.. ----- MacDermott wrote: ----
Just making sure we're on the same page

Sorry if I've made you feel foolish
that's a more difficult state to learn in

I did this:

I created a query something like this:
SELECT [FieldA] & [FieldB] AS MyField FROM MyTable

Then in Design View, I added Ascending to the Sort under MyField.

The SQL I got looked like this:
SELECT [FieldA] & [FieldB] AS MyField FROM MyTable ORDER BY [FieldA] &> [FieldB] .

Note that it did not say "ORDER BY MyField".
Based on this, I would recommend something like this:

lstBox.RowSource = LFSort(lstBox.RowSource, "InstallerFirstName & '

' &> InstallerLastName ")