Order By not asc - desc

[tjcinnamon]

I want to sort my query results by VendorNumber (which is an AutoNumber won't be sequential due to deletions). I want to sort the query by what vendor number the current form is on. So if my vendor numbers are 1, 2, 14, 16, 17 and the vendor number on the form is 14, I want the query to be sorted with the Items from vendor 14 first then 16, 17, 1, 2.

Your time is appreciated,
JOe K.

 

[Ghostjung]

Hello, I am not too sure if this is what you want (most likely it isn't...) but I'll give this a shot anyhows.

From what it sounds like, you want to display the results of a query in the the order of which VendorNumber your form is currently on, and then, move onto the end (and then back to the start).

I've managed to somewhat work out how to get the query to give you 14, 16, 17), but not too sure how to return it back to 1, 2.

Anyway, what I did was created a macro (found under Objects). First in action; add "OpenQuery" and place your 'query name'. The 2nd action is then to "ApplyFilter" where the condition is [query name]![VendorNumber]>=[Forms]![form name]![VendorNumber]

So, when you create a button on your form to run the macro, it should be able to detect what the current VendorNumber is, and the query will show you the results from that number and onwards (so 14, 16 and 17 in this case).

I guess if you make a 2nd macro with [query name]![VendorNumber]<[Forms]![form name]![VendorNumber] as the WHERE condition, it will be able to bring up 1 and 2... The downside to this method is that you will not be able to view both versions of the same query.

Hope that helps and makes sense.

 

[neileg]

Unusual request. Ghostjung's idea might work if you union the queries together.

Alternatively, you could create a new calculated field in the query, say

SortField: Iif([VendorNumber]<[Forms]![form name]![VendorNumber], [VendorNumber]+1000000, [VendorNumber])

This would add a million to vendor numbers below the one in the form so if you sort on this calculated field, those numbers less than the one in the form will shift to the bottom (until your vendor numbers reach a million of course!).

 

[namliam]

Alternatively, you could create a new calculated field in the query, say

SortField: Iif([VendorNumber]<[Forms]![form name]![VendorNumber], [VendorNumber]+1000000, [VendorNumber])

Unusual indeed, but I had a simular one some time ago I used something like this but this way doesnt guarantee your sort. I did it this way.

SortField: Iif([VendorNumber]<[Forms]![form name]![VendorNumber], 2, 1)

Then sort first on this sortfield, then second on the vendornumber.
This does guarantee the sortorder to be the way you desire.

 

[neileg]

Yes, namliam, I follow what you are doing, just not sure why my suggestion doesn't guarantee a correct sort.

 

[namliam]

Because -in theory- an autonumber can go to an infinate number, thus exceeding anything you add to it causing the sort to go whacko.

In practice adding 1.000.000 or even 100.000.000 would guarantee it to 99.9% of all cases but in some cases.... and allways where AND when you wouldnt expect it. Better to have something in place that is 100% than 99.9% if the solution is -more or less- the same ....

 

[neileg]

OK, you're right, namliam. But as my user title says, I'm just a dirty hacker!

 

[namliam]

I am/used to be a great fan for the 80/20 rule and am as you say a brother "quick hacker"