Sorting Problem (1 Viewer)

[ErinL]

Hi everyone -

I have the following in the RowSource of a list box:

SELECT Period FROM qryFiscalPds UNION SELECT "*" FROM qryFiscalPds

Period is a numeric field and sorts perfect in qryFiscalPds but when I use the line above in the list box it sorts like this:

*
1
10
11
12
13
2
3

I need to have the "*" in there so they can choose all periods if they want.

Can someone tell me why this happens and how to change it so it looks like this:

*
1
2
3
4
5
6
7
8
9
10
11
12
13

Thank you in advance!!

 

[missinglinq]

The problem, apparently, is that by adding a non-numerical character, i.e. the asterisk, to the column, you're changing it from Numeric to Alpha, from the standpoint of the Access Gnomes, and that's how they're sorting your column, alphabetically! Only workaround I can think of would be to use something like 9999 in your UNION statement, instead of the asterisk, which will keep your sort Numeric, but put the 9999 at the bottom of the Combobox, or 0, which would also keep it Numeric, but would put it at the top. Your users would simply have to learn that selecting this value means 'select all.'

Maybe someone with more skills in SQL will be able to offer a better solution.

Linq ;0)>

 

[ErinL]

Thank you for the reply, Linq!

I will try your suggestion of using a numeric value for the "all" selection and will just add a text box with a note on which to choose for "all" for the users.

 

[CJ_London]

you could always use the format function to insert preceding zeros

SELECT format(Period,"00") FROM qryFiscalPds UNION SELECT "*" FROM qryFiscalPds

Which will give you

*
01
02
03
04
05
06
07
08
09
10
etc

 

[spikepl]

Try
SELECT Period, Period AS P2 FROM qryFiscalPds UNION SELECT "*", 0 FROM qryFiscalPds SORT BY 2