• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

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
0
logopolis1969
Asked:
logopolis1969
  • 3
  • 2
  • 2
1 Solution
 
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now