Adding records relating to intervals between two dates

[DubboPete]

Hey all,

I have a form with two date fields, and I want to be able to write these two dates, AND the dates in between, to a table, to show when people are available when presented with the choice.

example:
date1 = 2012/5/25
date2 = 2012/5/29

results should add 4 entries to a table (date2 is the date they become unavailable again) and these 4 should be dated 2012/5/25, 2012/5/26, 2012/5/27 and 2012/5/28...

It's to present a list of people available on any given date. The peeps have the choices, they say when they are available, and we use that information for many purposes. Is this an easy bit of code, and where do I start to add the records as individual dated records to a table.

Any help much appreciated

DubboPete

 

[John W. Vinson]

Hey all,

I have a form with two date fields, and I want to be able to write these two dates, AND the dates in between, to a table, to show when people are available when presented with the choice.

example:
date1 = 2012/5/25
date2 = 2012/5/29

results should add 4 entries to a table (date2 is the date they become unavailable again) and these 4 should be dated 2012/5/25, 2012/5/26, 2012/5/27 and 2012/5/28...

It's to present a list of people available on any given date. The peeps have the choices, they say when they are available, and we use that information for many purposes. Is this an easy bit of code, and where do I start to add the records as individual dated records to a table.

Any help much appreciated

DubboPete

Unless you want to keep a longstanding archive of these records, I wouldn't
write them to any table at all. You can just store the start and end dates and
use an auxiliary table in a query to show the individual dates.

I'll routinely have a general purpose table named NUM with a single field N,
with values from 0 to 10000 or so. You can use a query such as:

SELECT <other fields>, DateAdd("d", N, [StartDate])
FROM yourtable, Num
WHERE N <= DateDiff("d", [StartDate], [EndDate]);
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[DubboPete]

Hey all,

I have a form with two date fields, and I want to be able to write thesetwo dates, AND the dates in between, to a table, to show when people are available when presented with the choice.

example:
date1 = 2012/5/25
date2 = 2012/5/29

results should add 4 entries to a table (date2 is the date they become unavailable again) and these 4 should be dated 2012/5/25, 2012/5/26, 2012/5/27 and 2012/5/28...

It's to present a list of people available on any given date.   The peeps have the choices, they say when they are available, and we use that information for many purposes.   Is this an easy bit of code, and where do Istart to add the records as individual dated records to a table.

Any help much appreciated

DubboPete

Unless you want to keep a longstanding archive of these records, I wouldn't
write them to any table at all. You can just store the start and end dates and
use an auxiliary table in a query to show the individual dates.

I'll routinely have a general purpose table named NUM with a single fieldN,
with values from 0 to 10000 or so. You can use a query such as:

SELECT <other fields>, DateAdd("d", N, [StartDate])
FROM yourtable, Num
WHERE N <= DateDiff("d", [StartDate], [EndDate]);
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Thanks John,

I actually do want to store them in a table for historical reference,
so can that code be amended to append to a table?

Table TblAvailabilty

Fields to be appended are
EmployeeID
InDate
Outdate

I like that idea you put forward though, I can already use that for
something else in the same db

TIA
Pete

 

[John W. Vinson]

I actually do want to store them in a table for historical reference,
so can that code be amended to append to a table?

Table TblAvailabilty

Fields to be appended are
EmployeeID
InDate
Outdate

I like that idea you put forward though, I can already use that for
something else in the same db

TIA
Pete

Sure. Just create it as a Select Query and then change it to an Append Query.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[DubboPete]

Sure. Just create it as a Select Query and then change it to an Append Query.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -

Thank you John, worked perfectly