Sort Order In a Query

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

--
Is it possible to have sort order in a query to what was last used, Last
used being at the top of the order?
Thanks for any help...............Bob






..........Jenny Vance
 
Bob said:
--
Is it possible to have sort order in a query to what was last used, Last
used being at the top of the order?
Thanks for any help...............Bob






.........Jenny Vance
Click to expand...

You need to provide a way of Access knowing what was used last (and I
assume next to last and next to next to last etc.. Access does not store
that information. In fact "Used Last" is not defined in Access and your
idea of what was last used, may not be mine. You need to start by defining
exactly what you want and going from there.
 
Ok, it was a drop down list of expenses that are is alpha order, just
thought it would be good if they stacked them self in order of when they
where selected to use, so then most common records would have been at the
top of the list....thanks Bob
 
Ok, it was a drop down list of expenses that are is alpha order, just
thought it would be good if they stacked them self in order of when they
where selected to use, so then most common records would have been at the
top of the list....thanks Bob
Click to expand...

Then you need to add a field (to the table that is used as the combo
row source) that stores the date the item was used. Add this new field
to the query. Then sort the query on that date in Descending order.
 
fredg said:
Then you need to add a field (to the table that is used as the combo
row source) that stores the date the item was used. Add this new field
to the query. Then sort the query on that date in Descending order.
Click to expand...

That's makes it sound so easy great thanks Fred.........Bob
 
Bob said:
That's makes it sound so easy great thanks Fred.........Bob
Click to expand...
I suppose there is something else I have to do , is to date/Time stamp it,
how do I go about that?
Thanks Bob
 
I suppose there is something else I have to do , is to date/Time stamp it,
how do I go about that?
Thanks Bob
Click to expand...

As default value in the date field write:
=Now()

When a new record is added, the current date and time will be saved in
this field. You can always over-write the current time if you wish.

Nothing can be done with your existing records however, unless you
wish to give each of those records an arbitrary date and time value.
 
fredg said:
As default value in the date field write:
=Now()

When a new record is added, the current date and time will be saved in
this field. You can always over-write the current time if you wish.

Nothing can be done with your existing records however, unless you
wish to give each of those records an arbitrary date and time value.
Click to expand...
Fred it wasn't when a new record was added that I really wanted to be date
time stamped , but when I used it, by selecting the record in my drop down
list. I have about 20 records and usually only use 4 or 5 of them so thought
it would be easier if those ones where always closer to the
top..............Thanks for your help.........Bob
 
Fred it wasn't when a new record was added that I really wanted to be date
time stamped , but when I used it, by selecting the record in my drop down
list. I have about 20 records and usually only use 4 or 5 of them so thought
it would be easier if those ones where always closer to the
Click to expand...

YOu'll need to run an Update query from the AfterUpdate event of the
combo box (using VBA code to execute the query).

I will say two things though: 20 rows is a TINY combo box (2000 is
big, 20000 is too big). And if I were a user, I'd get the screaming
meemies from a combo that a) was not in any discernable logical order
b) didn't allow autocomplete and c) kept reordering itself as it was
used!

John W. Vinson[MVP]
 
Ok John. point taken!
Thanks.bob

John Vinson said:
YOu'll need to run an Update query from the AfterUpdate event of the
combo box (using VBA code to execute the query).

I will say two things though: 20 rows is a TINY combo box (2000 is
big, 20000 is too big). And if I were a user, I'd get the screaming
meemies from a combo that a) was not in any discernable logical order
b) didn't allow autocomplete and c) kept reordering itself as it was
used!

John W. Vinson[MVP]
Click to expand...
 
Bob said:
Ok, it was a drop down list of expenses that are is alpha order, just
thought it would be good if they stacked them self in order of when
they where selected to use, so then most common records would have
been at the top of the list....thanks Bob
Click to expand...

Maybe you want to use the one with the highest access count and not the
last date?
 
Bob said:
That's sounds good Joseph , how do I go about that?
Thanks.........Bob
Click to expand...

Very similar to the procedure to that suggested except you use a numeric
field and add one at each event. The code is easy, but finding the right
trigger may be more of a problem. In this case it might not be a problem if
someone were to go directly to the table and make the change when you used a
trigger on the form. It would just be one off and I would guess for your
use, that would not be much of a problem. There are a number of ways of
approaching it.
 
Back
Top