Union Query not sorting correctly

[Max Moor]

Hi All,

I have a union query, in Access 2002, that I'm using in a combo box to
get an "All" option. That part works fine.

The problem is that I want the choices in the box to be sorted by the
ID value. In the underlying table, the ID field is an Autonumber. There are
currently 12 records. The sort comes out:

1
10
11
12
2
3
4
....

instead of:

1
2
....
9
10
11
12

The SQL for the combo box follows:

SELECT tblPurchaseGroups.PurchaseGroupID, tblPurchaseGroups.PurchaseGroup
FROM tblPurchaseGroups
WHERE (tblPurchaseGroups.PurchaseGroupID <> 1)
UNION Select "All" as AllChoice, "All" as Bogus
FROM tblPurchaseGroups
ORDER BY tblPurchaseGroups.PurchaseGroupID;

So, why is the sort wrong? Does the union with "All" mess something
up? How can I make it right?

- Max

 

[Allen Browne]

"All" is not a number. It is a piece of Text. The only way Access can
include a word like that in the field is to treat it as a text field.
Consequently, the field is sorted as text.

The best solution would be to rethinnk your logic, and avoid sticking
non-numeric values into a numeric field.

If you really can't do that, a horrid and inefficient workaround might be to
sort by the Val() of the Text. Don't forget to use Nz(), becuase Val() barfs
on nulls.

 

[Steve Schapel]

Max,

Try it like this:

SELECT tblPurchaseGroups.PurchaseGroupID, tblPurchaseGroups.PurchaseGroup
FROM tblPurchaseGroups
WHERE (tblPurchaseGroups.PurchaseGroupID <> 1)
UNION SELECT 0 As PurchaseGroupID, "All" as PurchaseGroup
FROM tblPurchaseGroups
ORDER BY tblPurchaseGroups.PurchaseGroupID;

 

[Max Moor]

Max,

Try it like this:

SELECT tblPurchaseGroups.PurchaseGroupID, tblPurchaseGroups.PurchaseGroup
FROM tblPurchaseGroups
WHERE (tblPurchaseGroups.PurchaseGroupID <> 1)
UNION SELECT 0 As PurchaseGroupID, "All" as PurchaseGroup
FROM tblPurchaseGroups
ORDER BY tblPurchaseGroups.PurchaseGroupID;

Hi Steve (& Allen),

I used the syntax above for the combobox... I have a listbox query on
a form based on this combobox. I also have a few options checkboxes on the
form for hiding various categories of records in the listbox. When these
are checked or unchecked, I "rebuild" the listbox's SQL, which includes
futzing around with the combobox.

Anyway, I set the combobox SQL as above, then changed the logic in my
listbox SQL function to match... it is a beautiful thing now.

Thanks guys,
Max