Update query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a start date in a table column. I need to calculate the end date as
"next row's start date minus 1 day). In the exapmle I need the result: start
date 13/07/1998 - end date 31/03/1999 in the first data row. This is easy in
excel, but is there a way of achieving this in access?
Start Date
13/07/1998
01/04/1999
 
Hi

In your example let's say,

CurrentStartDate = 13/07/1998
CurrenttEndDate = To Be Calculated
StartDate is relevant column in table
MyTable is the table

CurrentEndDate = dateadd("d", -1, DMIN("StartDate", "MyTable", "StartDate >
#" & format(CurrentStartDate, "mm/dd/yyyy") & "#"))

Note: This assumes there always is a record with a later start date than the
current record.

Regards

Andy Hull
 
Andy, you saved my bacon. thanks 1000 times

Andy Hull said:
Hi

In your example let's say,

CurrentStartDate = 13/07/1998
CurrenttEndDate = To Be Calculated
StartDate is relevant column in table
MyTable is the table

CurrentEndDate = dateadd("d", -1, DMIN("StartDate", "MyTable", "StartDate >
#" & format(CurrentStartDate, "mm/dd/yyyy") & "#"))

Note: This assumes there always is a record with a later start date than the
current record.

Regards

Andy Hull
Click to expand...
 
Just to highlight something Andy glossed over.

While it's obvious from your example that your Short Date format is
dd/mm/yyyy, be aware that when dealing with dates in Access, you MUST use
mm/dd/yyyy format (or an unambiguous format such as yyyy-mm-dd or dd mmm
yyyy). While Access will correctly interpret 13/07/1998 as 13 Jul, 1998
(since there is no 13th month), it will ALWAYS interpret 12/07/1998 as 07
Dec, 1998 in queries.

For more information, see what Allen Browne has at
http://www.allenbrowne.com/ser-36.html, or what I had in my Sept, 2003
"Access Answers" column in Pinnacle Publication's "Smart Access". You can
download the column (and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html
 
Back
Top