If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sorting A-Z
Hi,
I have a Payment Method table with two fields (Payment ID, and Payment Method). In the Payment method I have cash, chase, visa, money order, american express, western union, wire. These payment methods are text type, and they are sorted alphabetically. I want to add another record "Other", and I want to keep this always as the last option when creating a combo box in a form. Currently it is sorting “other” along with the rest of the payment methods. How can I make “other” appear at the bottom of the list? Thanks. John |
#2
|
|||
|
|||
Sorting A-Z
You can never rely on the order of records in tables: relational database
theory says that the DBMS is allowed to put the data wherever it wants to. If the order of data is important, you must use a query with an appropriate ORDER BY clause. Assuming that you're using this for a combo box, you could make the RowSource of the combo box something like: SELECT PaymentID, PaymentMethod FROM PaymentMethod ORDER BY IIf([PaymentMethod] = "Other", "ZZZZ", [PaymentMethod]) Note that I removed the spaces in your field and table names. Spaces are seldom a good idea. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "John" wrote in message news Hi, I have a Payment Method table with two fields (Payment ID, and Payment Method). In the Payment method I have cash, chase, visa, money order, american express, western union, wire. These payment methods are text type, and they are sorted alphabetically. I want to add another record "Other", and I want to keep this always as the last option when creating a combo box in a form. Currently it is sorting "other" along with the rest of the payment methods. How can I make "other" appear at the bottom of the list? Thanks. John |
#3
|
|||
|
|||
Sorting A-Z
Hi Douglas,
Suppose my SQL is in the following format: SELECT DISTINCTROW [Payment Methods].* FROM [Payment Methods] ORDER BY [Payment Methods].PaymentMethod; How can I rewrite this to match your code below? Everytime I do that I get syntax errors. John "Douglas J. Steele" wrote: You can never rely on the order of records in tables: relational database theory says that the DBMS is allowed to put the data wherever it wants to. If the order of data is important, you must use a query with an appropriate ORDER BY clause. Assuming that you're using this for a combo box, you could make the RowSource of the combo box something like: SELECT PaymentID, PaymentMethod FROM PaymentMethod ORDER BY IIf([PaymentMethod] = "Other", "ZZZZ", [PaymentMethod]) Note that I removed the spaces in your field and table names. Spaces are seldom a good idea. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "John" wrote in message news Hi, I have a Payment Method table with two fields (Payment ID, and Payment Method). In the Payment method I have cash, chase, visa, money order, american express, western union, wire. These payment methods are text type, and they are sorted alphabetically. I want to add another record "Other", and I want to keep this always as the last option when creating a combo box in a form. Currently it is sorting "other" along with the rest of the payment methods. How can I make "other" appear at the bottom of the list? Thanks. John |
#4
|
|||
|
|||
Sorting A-Z
Douglas,
I got it to work. You're awsome. Thank you so much! John "Douglas J. Steele" wrote: You can never rely on the order of records in tables: relational database theory says that the DBMS is allowed to put the data wherever it wants to. If the order of data is important, you must use a query with an appropriate ORDER BY clause. Assuming that you're using this for a combo box, you could make the RowSource of the combo box something like: SELECT PaymentID, PaymentMethod FROM PaymentMethod ORDER BY IIf([PaymentMethod] = "Other", "ZZZZ", [PaymentMethod]) Note that I removed the spaces in your field and table names. Spaces are seldom a good idea. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "John" wrote in message news Hi, I have a Payment Method table with two fields (Payment ID, and Payment Method). In the Payment method I have cash, chase, visa, money order, american express, western union, wire. These payment methods are text type, and they are sorted alphabetically. I want to add another record "Other", and I want to keep this always as the last option when creating a combo box in a form. Currently it is sorting "other" along with the rest of the payment methods. How can I make "other" appear at the bottom of the list? Thanks. John |
Thread Tools | |
Display Modes | |
|
|