Adding records relating to intervals between two dates

  • Thread starter Thread starter DubboPete
  • Start date Start date
D

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
 
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
Click to expand...

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
 
Hey all,
Click to expand...
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.
Click to expand...
example:
date1 = 2012/5/25
date2 = 2012/5/29
Click to expand...
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...
Click to expand...
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.
Click to expand...
Any help much appreciated :)
Click to expand...
DubboPete
Click to expand...

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
Click to expand...

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
 
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
Click to expand...

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
 
Back
Top