Field for End Date..Please help!

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

Guest

Here is what I have been given to complete..

I have one field labeled "Start Date" with data in the field. I have no
field that I would like to say and do the following.. Here is an example..

A B
Start Date End date

07/02/2006 07/11/2006
07/12/2006 08/12/2006
08/13/2006 08/28/2006
08/29/2006 present

I need a query that will put in the end date which will read from the start
date. Example.. Look at the first date in Colunm B. Notice that it is one day
BEFORE the second Date in Column A. Same goes for the Second date in Colunm B
is one day before the third date in Column A. I need to have a field named
End Date that will automatically put one day prior to the next day from
colunm A. How do I do this in a query. I hoep this makes sense. Please help!

I
 
To be more clear. I figured out how to add the end date to the query etc. But
I need to figure out how to start the calculations from the SECOND date in
column A. Once I have that I am home free. I did this End Date: [start
date]-1 but it gives me one day earlier from the 1st date in column A. I need
it to start from the Second date in column A then it will continu thoughout
the filed on its own correct?
 
Here is what I have been given to complete..

I have one field labeled "Start Date" with data in the field. I have no
field that I would like to say and do the following.. Here is an example..

A B
Start Date End date

07/02/2006 07/11/2006
07/12/2006 08/12/2006
08/13/2006 08/28/2006
08/29/2006 present

I need a query that will put in the end date which will read from the start
date. Example.. Look at the first date in Colunm B. Notice that it is one day
BEFORE the second Date in Column A. Same goes for the Second date in Colunm B
is one day before the third date in Column A. I need to have a field named
End Date that will automatically put one day prior to the next day from
colunm A. How do I do this in a query. I hoep this makes sense. Please help!

I
Click to expand...

This is really a spreadsheetish kind of operation... but it's doable.

What you can do is use DMin to find the earliest start date later than
the current record's start date:

SELECT [Start Date], DateAdd("d", -1, NZ(DMin("[Start Date]",
"yourtablename", "[Start Date] > #" & [Start Date] & "#"), Date() +
1))

If there is no start date greater than this record's start date, the
NZ() function will use today's date plus one instead.


John W. Vinson[MVP]
 
John,

I cut and paste the below SQL statement and nothing happened. I added my
table name then cut and paste. Am I missing something? It seems I am on the
right track with this code but still teaking it! Any advise? thanks again

John Vinson said:
Here is what I have been given to complete..

I have one field labeled "Start Date" with data in the field. I have no
field that I would like to say and do the following.. Here is an example..

A B
Start Date End date

07/02/2006 07/11/2006
07/12/2006 08/12/2006
08/13/2006 08/28/2006
08/29/2006 present

I need a query that will put in the end date which will read from the start
date. Example.. Look at the first date in Colunm B. Notice that it is one day
BEFORE the second Date in Column A. Same goes for the Second date in Colunm B
is one day before the third date in Column A. I need to have a field named
End Date that will automatically put one day prior to the next day from
colunm A. How do I do this in a query. I hoep this makes sense. Please help!

I
Click to expand...

This is really a spreadsheetish kind of operation... but it's doable.

What you can do is use DMin to find the earliest start date later than
the current record's start date:

SELECT [Start Date], DateAdd("d", -1, NZ(DMin("[Start Date]",
"yourtablename", "[Start Date] > #" & [Start Date] & "#"), Date() +
1))

If there is no start date greater than this record's start date, the
NZ() function will use today's date plus one instead.


John W. Vinson[MVP]
Click to expand...
 
John,

I made a very small adjustment and it works perfectly. Thanks you so much
for all your help!

John Vinson said:
Here is what I have been given to complete..

I have one field labeled "Start Date" with data in the field. I have no
field that I would like to say and do the following.. Here is an example..

A B
Start Date End date

07/02/2006 07/11/2006
07/12/2006 08/12/2006
08/13/2006 08/28/2006
08/29/2006 present

I need a query that will put in the end date which will read from the start
date. Example.. Look at the first date in Colunm B. Notice that it is one day
BEFORE the second Date in Column A. Same goes for the Second date in Colunm B
is one day before the third date in Column A. I need to have a field named
End Date that will automatically put one day prior to the next day from
colunm A. How do I do this in a query. I hoep this makes sense. Please help!

I
Click to expand...

This is really a spreadsheetish kind of operation... but it's doable.

What you can do is use DMin to find the earliest start date later than
the current record's start date:

SELECT [Start Date], DateAdd("d", -1, NZ(DMin("[Start Date]",
"yourtablename", "[Start Date] > #" & [Start Date] & "#"), Date() +
1))

If there is no start date greater than this record's start date, the
NZ() function will use today's date plus one instead.


John W. Vinson[MVP]
Click to expand...
 
John:

I hope you can help me with this one...

I have a situation similar to TKM, however, I have a field in addition to
[Start Date] called [Staff ID]. I want my query to add End Dates based on
the same [Staff ID]. Is there a way to do this?

Thanks!
--
Julie


John Vinson said:
Here is what I have been given to complete..

I have one field labeled "Start Date" with data in the field. I have no
field that I would like to say and do the following.. Here is an example..

A B
Start Date End date

07/02/2006 07/11/2006
07/12/2006 08/12/2006
08/13/2006 08/28/2006
08/29/2006 present

I need a query that will put in the end date which will read from the start
date. Example.. Look at the first date in Colunm B. Notice that it is one day
BEFORE the second Date in Column A. Same goes for the Second date in Colunm B
is one day before the third date in Column A. I need to have a field named
End Date that will automatically put one day prior to the next day from
colunm A. How do I do this in a query. I hoep this makes sense. Please help!

I
Click to expand...

This is really a spreadsheetish kind of operation... but it's doable.

What you can do is use DMin to find the earliest start date later than
the current record's start date:

SELECT [Start Date], DateAdd("d", -1, NZ(DMin("[Start Date]",
"yourtablename", "[Start Date] > #" & [Start Date] & "#"), Date() +
1))

If there is no start date greater than this record's start date, the
NZ() function will use today's date plus one instead.


John W. Vinson[MVP]
Click to expand...
 
Back
Top