Population of a field from a date range in a tablke

I have a start date and an end date in a table. I want to list in a new field in either a separte query or table every date in between those dates, and each time a new record is added to the original table more dates are added to this list.
Is this possible.

John
logopolis1969Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jjafferrCommented:
in a query:

have this line in the criteria of both the start and end dates

Between #[Forms]![Your Form Name]![StartDate]# And #[Forms]![Your Form Name]![EndDate]#

this will list all the dates in between.

you will have to run the query everytime a date is entered.

jaffer
0
Rey Obrero (Capricorn1)Commented:
first get the min start date and max end date

select min([start date]) as mStartDate, max([End Date]) as mEndDate
from tablename.

place this codes in a form

private sub btnCreateDateField_Click()

dim sql as string, rs as DAO.recordset, sDate, eDate, rDate

sql="select min([start date]) as mStartDate, max([End Date]) as mEndDate from tablename"

set rs=currentdb.openrecordset(sql)

sdate=rs("mStartDate")
edate=rs("mEnddate")


Do Until rDate >= eDate
    If Not IsDate(rDate) Then
    iSql = "insert into tblDates(recDate) "
    iSql = iSql & "Values(#" & sDate & "#)"
    CurrentDb.Execute iSql, dbFailOnError
   
    rDate = DateAdd("d", 1, sDate)
    Else
    iSql = "insert into tblDates(recDate) "
    iSql = iSql & "Values(#" & rDate & "#)"
    CurrentDb.Execute iSql, dbFailOnError

    rDate = DateAdd("d", 1, rDate)
   
    End If
Loop

end sub


0
Rey Obrero (Capricorn1)Commented:
change this

Do Until rDate >= eDate

to

Do Until rDate = eDate
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

logopolis1969Author Commented:
Hi Jaffer

Thanks for your reply. I asume I have to create a form linked to the query with the relevant fields in. I have done this and called it form1. I have therefore entered the following code in the StartDate criteria in the query: Between#[Forms]![form1]![StartDate]# And #[Forms]![form1]![EndDate]# but it will not let me move on to put it in the other field (EndDate) giving me the error message "the expression you have entered has an invalid date value".

One other question, where will the output be displayed?

John
0
logopolis1969Author Commented:
Hi Capricorn1

I have tried your code on my form. It does export dates but the dates are not correct for the dates in between the start and finish date. This is the code I have put in:

Private Sub Toggle12_Click()

Dim sql As String, rs As DAO.Recordset, sDate, eDate, rDate

sql = "select min([StartDate]) as mStartDate, max([EndDate]) as mEndDate from terms"

Set rs = CurrentDb.OpenRecordset(sql)

sDate = rs("mStartDate")
eDate = rs("mEnddate")


Do Until rDate = eDate
    If Not IsDate(rDate) Then
    iSql = "insert into tblDates(recDate) "
    iSql = iSql & "Values(#" & sDate & "#)"
    CurrentDb.Execute iSql, dbFailOnError
   
    rDate = DateAdd("d", 1, sDate)
    Else
    iSql = "insert into tblDates(recDate) "
    iSql = iSql & "Values(#" & rDate & "#)"
    CurrentDb.Execute iSql, dbFailOnError

    rDate = DateAdd("d", 1, rDate)
   
    End If
Loop

End Sub

Have I done something stupid?

I tested it by putting in a start date of 1/1/06 and an end date of 27/2/06 and came out with a range of  57 dates right up to December 2006 as below.

ID      recDate
1992      01/01/2006
2023      02/01/2006
2004      13/01/2006
2005      14/01/2006
2006      15/01/2006
2007      16/01/2006
2008      17/01/2006
2009      18/01/2006
2010      19/01/2006
2011      20/01/2006
2012      21/01/2006
2013      22/01/2006
2014      23/01/2006
2015      24/01/2006
2016      25/01/2006
2017      26/01/2006
2018      27/01/2006
2019      28/01/2006
2020      29/01/2006
2021      30/01/2006
2022      31/01/2006
1993      01/02/2006
2024      02/02/2006
2035      13/02/2006
2036      14/02/2006
2037      15/02/2006
2038      16/02/2006
2039      17/02/2006
2040      18/02/2006
2041      19/02/2006
2042      20/02/2006
2043      21/02/2006
2044      22/02/2006
2045      23/02/2006
2046      24/02/2006
2047      25/02/2006
2048      26/02/2006
1994      01/03/2006
2025      02/03/2006
1995      01/04/2006
2026      02/04/2006
1996      01/05/2006
2027      02/05/2006
1997      01/06/2006
2028      02/06/2006
1998      01/07/2006
2029      02/07/2006
1999      01/08/2006
2030      02/08/2006
2000      01/09/2006
2031      02/09/2006
2001      01/10/2006
2032      02/10/2006
2002      01/11/2006
2033      02/11/2006
2003      01/12/2006
2034      02/12/2006

Many thanks for your help so far.

John
0
jjafferrCommented:
Between#[Forms]![form1]![StartDate]# And #[Forms]![form1]![EndDate]#
            ^ you need a space here

so your line should be
Between #[Forms]![form1]![StartDate]# And #[Forms]![form1]![EndDate]#

jaffer
0
Rey Obrero (Capricorn1)Commented:
try using this format for date

sql = "select min(format([StartDate],"mm/dd/yyyy")) as mStartDate, max(format([EndDate],"mm/dd/yyyy")) as mEndDate from terms"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.