Sort Order In a Query

[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

 

[Joseph Meehan]

--
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

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.

 

[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

 

[fredg]

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

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.

 

[Bob]

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.

That's makes it sound so easy great thanks Fred.........Bob

 

[Bob]

That's makes it sound so easy great thanks Fred.........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

 

[fredg]

I suppose there is something else I have to do , is to date/Time stamp it,
how do I go about that?
Thanks Bob

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.

 

[Bob]

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.

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

 

[John Vinson]

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

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]

 

[Bob]

Ok John. point taken!
Thanks.bob

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]

 

[Joseph Meehan]

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

Maybe you want to use the one with the highest access count and not the
last date?

 

[Bob]

That's sounds good Joseph , how do I go about that?
Thanks.........Bob

 

[Joseph Meehan]

That's sounds good Joseph , how do I go about that?
Thanks.........Bob

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.