Previous and Next Record

  • Thread starter Thread starter Trish
  • Start date Start date
T

Trish

I am trying to find the closest date before and closest
date after a specific date in a query. I can do it by
creating 2 queries (one using the Max of all records less
than that date and one using the Min of all records
greater than that date).

Does anyone know of a better way?

Thanks for saving my hair!
 
Dear Trish:

You can get both these dates in separate columns within a single
query. You would use a subquery for each one. Possibly, you want the
previous and following dates from within a single group. This must be
included in the logic.

If you would post a simple query that shows which columns you want
besides the before and after dates, I would have something to start
with. Also, if you want the "closest date before and the closest date
after" from within a group (such as for the same customer) you would
need to specify what column(s) define a group. Otherwise, you're
going to see the closest dates out of the entire table.

I am trying to find the closest date before and closest
date after a specific date in a query. I can do it by
creating 2 queries (one using the Max of all records less
than that date and one using the Min of all records
greater than that date).

Does anyone know of a better way?

Thanks for saving my hair!
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top