MS Access 2003 - ordering the string values for a listbox not alphabetical
Here is a silly question. Lets say I have a query that produces for a list box, and it produces values for three stores
Store A 18
Store开发者_高级运维 B 32
Store C 54
Now if I ORDER BY in the sql statement the only thing it will do is descending or ascending alphabetically but I want a certain order (only because THEY WANT A CERTAIN ORDER) .....so is there a way for me to add something to the SQL to get
Store B
Store C
Store A
i.e. basically row by row what i want. thanks!
Add a numeric field, sequencer, to the table which contains the store names. Use the sequencer values to determine your sort order.
SELECT sequencer, store_name FROM YourTable ORDER BY sequencer;
In the list box, set the column width = 0 for the sequencer column.
Or simply, as @dscarr suggested, don't include sequencer in the SELECT
field list, but just include it in the ORDER BY
...
SELECT store_name FROM YourTable ORDER BY sequencer;
You can do 1 of 2 things.
Either use a SWITCH stament, something like
SELECT Table1.Store,
Table1.Val,
Switch([Store]="StoreB",1,[Store]="StoreC",2,[Store]="StoreA",3) AS Expr1
FROM Table1
ORDER BY Switch([Store]="StoreB",1,[Store]="StoreC",2,[Store]="StoreA",3);
Or use a secondary order table, that stores the values of the store names, and an order by value.
精彩评论