Combox Sort Order Issue

[gray]

Hi All
WinXPPro
Access 2002 SP3

I wonder if anyone knows how to cure a combobox sorting issue. From my research I seem to be doing everything OK? My combobox is not 'bound' to a table but is given a 'Table/Query' rowsource in VBA code. The SQL for the rowsource looks something like the example below:-

SELECT * FROM My_Table WHERE Invoice_No = xxx AND Deleted <> True ORDER BY Line_Type, Payment_No.

(I mask unwanted fields from the SELECT * by 0cm column definitions)

When run as a standalone query it sorts beautifully! However, when displayed in my combobox the Payment _No gets sorted thus:-

1
10
11
12 etc
2
21
22
23 etc

Seen this before I thought... I must have set my payment number set as a text field... but, no, it's set as below

My_Table has:-
1. An autonumbered Primary Key, namely 'Db_No'
2. The above Payment_No, defined as Long Integer, Indexed Yes, Duplicates OK
3. A Text Field of Line_Type (stock code)
4. Various other Date, text and numbered fields.

I tried adding leading zeros to the Payment_No in the 'raw' table but they simply disappear.. and in any case it's a number field and sorts correctly outside the
combobox so, it seems to me, that's a red herring.

I tried altering the bound column to the payment_no but no joy.

I took the Line_Type ORDER BY field out... still no joy

And there doesn't seem to be anything obvious in the combobox properties that needs setting/unsetting?

I am guessing this is because the combobox 'converts' the numbers to strings, hence the re-sorted display.

Any ideas anyone?

Thanks

 

[gemma-the-husky]

try using a stored sorted query instead - then you shouldnt get these issues

 

[BrettM]

Microsoft discuss this issue in depth here...
http://office.microsoft.com/en-us/access/HP051877861033.aspx

Look at the section about the list index.

Hope it helps.

 

[gray]

Hi Gemma n' Bret

Thanks for your ideas... I went via the QueryBuilder route as suggested and it exposed a discrepency' in my Select statement which my previous SQL calls (via the normal Query window) did not...(or, more likley, I messed up!)

In fact, Querybuilder sorted the resultset as 1,10,11 etc, 2,21,22 etc and clearly this is why the combobox, quite reasonably, displayed it that way.

I named the fields I needed explicitly, dabbled with the ORDER BY parameters and, as if by magic, it displayed as I wanted it to.

It's easy to look for really complicated reasons things don't work and miss the easy stuff!

Thanks again for your help!

Cheers