"Jumbled" sort in list box on form

J

JimP

I have code that creates a row source for a list box on a form.

"SELECT DISTINCT LCase(cust_id) FROM tblCustomers"

A particular user is reporting that the cust_id's in the list box are in no
particular order. This user starts their cust_id's with (2) digits and uses
both upper/lower case characters (e.g. 05Nwind)

I think DISTINCT should force a sort in alphanumeric order - any ideas?

Note: the LCase conversion has to do with eliminating duplicate codes in a
case sensitive SQL Server database (don't know if this could be the issue)
 
J

Jim Burke in Novi

I'm pretty sure you still need to add an ORDER BY clause:

SELECT DISTINCT LCase(cust_id) FROM tblCustomers
ORDER BY cust_id
 
F

fredg

I have code that creates a row source for a list box on a form.

"SELECT DISTINCT LCase(cust_id) FROM tblCustomers"

A particular user is reporting that the cust_id's in the list box are in no
particular order. This user starts their cust_id's with (2) digits and uses
both upper/lower case characters (e.g. 05Nwind)

I think DISTINCT should force a sort in alphanumeric order - any ideas?

Note: the LCase conversion has to do with eliminating duplicate codes in a
case sensitive SQL Server database (don't know if this could be the issue)
Click to expand...

1) Nowhere in your row source code do you have an Order By clause so
values are returned in no particular order.

Try:

"SELECT DISTINCT LCase([cust_id]) FROM tblCustomers Order By
LCase([cust_ID]);"
 
J

Jim Burke in Novi

I'm not sure if you can use a function in an order by statement. I think you
may need to name the field and use that name in the order by, e.g.

"SELECT DISTINCT LCase([cust_id]) AS C_ID FROM tblCustomers Order By C_ID"


fredg said:
I have code that creates a row source for a list box on a form.

"SELECT DISTINCT LCase(cust_id) FROM tblCustomers"

A particular user is reporting that the cust_id's in the list box are in no
particular order. This user starts their cust_id's with (2) digits and uses
both upper/lower case characters (e.g. 05Nwind)

I think DISTINCT should force a sort in alphanumeric order - any ideas?

Note: the LCase conversion has to do with eliminating duplicate codes in a
case sensitive SQL Server database (don't know if this could be the issue)
Click to expand...

1) Nowhere in your row source code do you have an Order By clause so
values are returned in no particular order.

Try:

"SELECT DISTINCT LCase([cust_id]) FROM tblCustomers Order By
LCase([cust_ID]);"
Click to expand...
 
Top