Combo Box Sorting Problem

[Guest]

Hello experts,

My report deals with data for wells. I have a combo box where the user will
choose the name of the well, then it will display the appropriate report. It
works great. However, the list in teh combo box shows as follows:

paramount 1
denver
Atlas
Bailey 1
Bailey 5
Bell 10
Bell 12
Bell 14
Bell 17
Bell 2
Bell 5
Bell 6
Borie 1
BELMOT 1
BELMOT 12
BELMOT 2
BELMOT 3

Obviously, the issues are: upper/lower case not consistent; the numbers
next to names are not sorted properly.

Is there a code that would allow no case sensitivity and sort names with
numbers appropriately?

I am using 2007, but have access to 2003.

Thank you so much for your help.
-
MB

 

[Guest]

the numbers next to names are not sorted properly.
You need to add a space or a zero to those like this --
Bell 02
Bell 05
Bell 06
to match
Bell 11
Use a calculated field for sorting like this ---
MySort: UCase([YourField])

 

[Guest]

Thank you, Karl, for answering so quickly! It works! Much appreciated!
--
MB

You need to add a space or a zero to those like this --
Bell 02
Bell 05
Bell 06
to match
Bell 11
Use a calculated field for sorting like this ---
MySort: UCase([YourField])

--
KARL DEWEY
Build a little - Test a little

Hello experts,

My report deals with data for wells. I have a combo box where the user will
choose the name of the well, then it will display the appropriate report. It
works great. However, the list in teh combo box shows as follows:

paramount 1
denver
Atlas
Bailey 1
Bailey 5
Bell 10
Bell 12
Bell 14
Bell 17
Bell 2
Bell 5
Bell 6
Borie 1
BELMOT 1
BELMOT 12
BELMOT 2
BELMOT 3

Obviously, the issues are: upper/lower case not consistent; the numbers
next to names are not sorted properly.

Is there a code that would allow no case sensitivity and sort names with
numbers appropriately?

I am using 2007, but have access to 2003.

Thank you so much for your help.
-
MB

 

[Guest]

Use something like the following as the row source for the combo box. Watch
out for word wrapping below.

SELECT Wells.Wells
FROM Wells
ORDER BY UCase(IIf(InStr([Wells]," ")>0, Left([Wells], InStr([Wells],"
")-1), [Wells])), IIf(InStr([Wells]," ")>0, Val(Mid([Wells], InStr([Wells],"
"))), 0);