any quick way to custom sort?

[airbaal]

I am using Access 2000 for a VB program with ADO 2.5. I am looking for a quick way to sort my records based on a unique, custom order. I know about the ORDER BY statement but it doesn't work in this case (or I don't know how do it...).

A stupid example would be a field called "Invoice Status". You can choose Not Paid, Open, Paid, and Unlikely.

I would want the "Not Paid" records first, then "Open", then "Unlikely", then finally "Paid". This recordset would be displayed on a grid list.

Just curious...

Eric

[paulw]

ORDER BY is definitely the way to do it. Is the control you are displaying on, overriding the delivered order?

Formulate the query as a simple SELECT query in Access and ensure that you are getting the desired results and then cut and paste the SQL definition from the query design window into VB.

Cheers,

P.

[airbaal]

I can get it listed on the grid in either ASC or DESC order no prob, but how do I override the order?

I have an example like:

strSQL = "SELECT * FROM myTable ORDER BY inv_status ASC"

rs.cursorlocation = adUseClient
set mshflexgrid1.datasource = rs

This would display in the order:
not paid, open, paid, unlikely

What if I want the order different and not ASC or DESC? Such as not paid, open, unlikely, and paid.

Thanks for the quick reply.

Eric

[paulw]

If you want the data displayed in a non-contiguous manner then your options are to use an extra dummy field or to change your inv_status field.

You could make your inv_status a numeric value and associate a display string with it (i.e. 1 = not paid, 2 = open, 3 = unlikely, 4 = paid). the ORDER BY will then produce what you want. The alternative is to add a sort field that picks up those values and order by that field.

If you are going to want different sort orders then I would add a [Sort order] field and allow it to be updated via a form.

ASC and DESC simply give you A-Z or Z-A sorting within a field and fields are compared character by character. Thus "not paid" will ALWAYS come before "paid" in ASC since "n" is before "p" alphabetically.

Cheers,

P.