List 4

[mrkshpntf]

I have simple data table (single column) which contains a list of items that is the row source for a combo box.

As of now, the items in the list are sorted in alphabetical order but I want it to display the items by the order of my choice.

I tried the remove sort option but the order persists.

Please help.

Thanks,
mrkshpntf

 

[PHV]

by the order of my choice
And your choice is ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Remou]

You will need to add another 'sort' field to the table.

 

[mrkshpntf]

PHV,

The order of my choice is non-alphabetic.

I want 2 - 3 items on the list (which are commonly chosen by the user) to appear at the top of the list.

Thanks,
mrkshpntf.

 

[PHV]

A starting point for the RowSource:
SELECT theItem FROM theTable ORDER BY IIf(theItem IN ('item1','item2',item3'),0,1), theItem

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mrkshpntf]

PHV,

I am sorry I don't follow what you mean by "starting point of the RowSource".

I tried using your query as the RowSource but the result is a list with only the same item listed throughout.


Thanks,
mrkshpntf.

 

[PHV]

What are the table name, field name and commonly chosen values ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mrkshpntf]

PHV,

They are as follows:

Table name: payMethod (with single column)
Field name: payMethod
Values: CC, Cash, DC, MO, CCheck, EMT

Prefered Order:
1 - CC, (Credit Card)
2 - DC, (Debit Card)
3 - EMT, (Electronic Money Transfer)
4 - Cash,
5 - CCheck, (Cashier's Check)
6 - MO (Money Order)

Thanks,
mrkshpntf.

 

[PHV]

Why not simply add a numeric field to the table (as already suggested by Remou) ?
Anyway, to keep with my initial suggestion, you may try this RowSource:
SELECT P.payMethod FROM payMethod P ORDER BY IIf(P.payMethod In ('CC','DC','EMT'),0,1), P.payMethod

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[randy700]

Change your table by adding a "Priority" field. You can then include that field in the combo box for sorting purposes. If your "preferred order" changes, you simply update the field in the combo box.

Randy

 

[mrkshpntf]

Randy700,

Your suggestion worked.

I added a column "Priority" and gave the items priority numbers.

Then changed RowSource to query:

SELECT payMethod
FROM payMethod
ORDER BY Priority;


Randy and PHV, thanks for your help and time. They are very much appreciated.

mrkshpntf.

 

[Remou]

 

[mrkshpntf]

Sorry Remou,

I forgot to thank you.

Thank you for your help. Yes, it was your idea originally.

Sorry again.

mrkshpntf.

 

[Remou]

 

[ProgramError]

pity I missed this thread - Ive just done what you wanted to do with a priority field. But created a form where the usdr could move any of the items up or down the list with up and cown controls. similar the switchboard designer allows you to.

Let me know if anyone is intrested in the coding for this and I'll put it in the FAQ's

regards


Ian Mayor (UK)
Program Error
Programming is 1% coding, 50% error checking and 49% sweat as your application bombs out in front of the client.