Stacking Date/Time from Row to Row

[Joshua]

Hello there.

I'm trying to run an update query that will look at a previous row's end
date and fill in the same date in the current row's start date. I've tried
using dlast to do this but have read that it doesn't see the rows in a table
in any particular order.

My goal is to stack dates and times to start and end one right after
another. This might not be possible but I would appreciate any assistance if
you can provide.

Thank you in advance.

 

[Jeff Boyce]

Joshua

Access stores data in tables like a "bucket o' data", for all practical
purposes. Unless YOU tell Access which order, it will use its own.

It almost sounds like you want to be able to look in the table and see the
records "in sequence". If so, be aware, Access tables aren't intended for
display. Use queries and forms instead.

On the other issue, how to use the "previous" record's end-date as a
start-date in the current row, again, how are YOU identifiying which record
is the previous one? Unless you can tell Access how, I don't see how Access
can guess...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Joshua]

Hi Jeff,

Thank you for your reply to this.

I am actually creating a table with grouping and sorting but I don't know if
that's good enough to "tell access which order to use" or not. Probably not.

We aren't actually using access in the way it should be used for this which
might be part of the problem. We are just using access to import data,
manipulation that data, and then exporting that data back out.

I can create a key field or a creation (date) field but since the data is
being appended to the table as it's being created, I don't know if that will
work.

Thanks again for your help and hopefully I've said something that makes
sense here.

 

[John Spencer]

With a small number of records you can do this using the VBA DMax Function.

UPDATE YourTable
SET [StartDate] =
DMAX("EndDate","YourTable","EndDate<" & Format(EndDate,"\#yyyy-mm-dd\#"))
WHERE [StartDate] is Null

I've ASSUMED that you can figure out the previous record using the END dates
of the current and prior records.

If you can't put the records in order by some means then post back.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County