Sorting A-Z

[John]

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

 

[Douglas J. Steele]

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.

 

[John]

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

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

(no private e-mails, please)

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

 

[John]

Douglas,

I got it to work. You're awsome.
Thank you so much!

John

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

(no private e-mails, please)

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