Sorting some numbers in tables 1

[JunglesMcBeef]

G'day,

I have a slight problem. I have some records in a table with a field that stores numbers. Some of the records do not have an entry in that field and I have left them blank. However, when I run a query and sort the number column ascendingly, the records with the blank value are above the ones with actual values in them.
eg.
This is what I get This is what I want
-------------------- --------------------
(blank) 1
(blank) 2
(blank) 3
1 4
2 5
3 6
4 7
5 8
6 9
7 10
8 (blank)
9 (blank)
10 (blank)

The problem for me is that it is part of a query and I would prefer it if I didn't have to write some code to do it, I am trying to keep my UI as simple as possible.

 

[WayneRyan]

Jungles,

You can make just a little change to any of your existing
queries to do this.

Make a new column:

Newcolumn: IIf(IsNull([SomeField), 999999, [SomeField])

Then change the order by to Ascending, and check to indicate that the field is not visible.

To handle the ties, you might also sort no Name as a secondary sort (to handle the "group" of nulls).

Wayne

 

[JunglesMcBeef]

Ta for that Wayne, but I need the field to be visible so I guess I'll just have to put up with the 99999999 filler values....unless any other ideas pop into our heads.

 

[WayneRyan]

Jungles,

No! I don't mean to replace your field, this entails
making a NEW field that is invisible and will only be
used for sorting purposes. It will have no other effect
than that. It exists only in the query, not the table.

Wayne

 

[JunglesMcBeef]

AAAhhhhh, cheers mate.