If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Stacking Date/Time from Row to Row
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. |
#2
|
|||
|
|||
Stacking Date/Time from Row to Row
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" wrote in message ... 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. |
#3
|
|||
|
|||
Stacking Date/Time from Row to Row
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. "Jeff Boyce" wrote: 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" wrote in message ... 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. . |
#4
|
|||
|
|||
Stacking Date/Time from Row to Row
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 Joshua wrote: 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. |
Thread Tools | |
Display Modes | |
|
|