Joined: 13 Aug 2007
|Posted: Mon Aug 13, 2007 8:00 am Post subject: force combo boxes to list non-alphabetically? [solved]
It is possible to force a combo box to list the options in a particular way? I want to be able to display months in the order January,February,March etc and not April,August,February!
The months are taken from a table called 'months' which has a key numeric field to put them in the correct order. But combo boxes do not appear to allow you to select in the same way as list boxes.
In the combo I am using SELECT DISTINCT "month" FROM "months" where as in a list box which displays possible events I am using SELECT "description", "event_id" FROM "event_type" where 'description' is is just a text display in the form and the actual record holds 1,2,3,4,5 etc. I want to allow the user to be able to just type the first 1 or two letters of the month (hence the combo box) or select from a pull down.
Hope someone can advise.
OK after some mucking around here's the answer for anyone who is stuck like I was.
1. Create a table with 2 columns, a and b. Put numeric values in a in the order you want the stuff in b to be displayed in the combo box.
Create a combo box on the form, right click and select control, or column if it's part of a sheet (?) view.
In the data tab select sql as type of list contents and then stick the following sql in the list content. You will need to replace A and B with the actual names of the items in your <table> which you will also have to change
SELECT DISTINCT "B","A" FROM "<table>" ORDER BY "A"
So if you are selecting month names from a table called months where A is the numeric month (month_id, 1 = January, 2 = February etc) and month is its name the sql is
SELECT DISTINCT "month","month_id" FROM "months" ORDER BY "month_id"