How to combine the date

[nebula81]

Hi, I have a table that contains name, start date, end date. My problem is, i want to combine the date to one list by using query. Sample of data as following:

Name Start_Date End_Date
Sarah 23-Feb-13 25-Feb-13
John 20-Feb-13 22-Feb-13
MIke 20-Feb-13 21-Feb-13

I want the result to be like this:



Combine_Date
20-Feb-13
21-Feb-13
22-Feb-13
23-Feb-13
24-Feb-13
25-Feb-13


Please help.

Thank you

[recyan]

Check out if Union Query is of any help.

Thanks

[nebula81]

I have tried...but 24-Feb13 not in the list.

[recyan]

I have tried...but 24-Feb13 not in the list.

oops, sorry,
missed out that 24th is not present in the 2 columns.
Just hold on till some one comes along
else
Will try to revert asap.

Thanks

[recyan]

Currently do not know how to do this with a standalone query.
Till some one comes along,
If you are open to storing the generated dates in a New Table, then,
save the below function & run it. The table name has been taken as myTable.

Public Function GenerateDatesForReport_1()
    Dim dbs As Database, tbl As TableDef, RepDate As Field, strTableName As String, tdf As TableDef
    Set dbs = CurrentDb
    
    ' _________________________________________________________________________
    ' Get the Start Date & End Date to generate the table tblReportDates
    Dim sqlStartEndDate As String
    Dim rst As DAO.Recordset
    
    sqlStartEndDate = "SELECT Min([Start_Date]) AS StartDate1, Max([End_Date]) AS EndDate1 FROM myTable"
    Debug.Print sqlStartEndDate
    Set rst = dbs.OpenRecordset(sqlStartEndDate)
    
    Dim StartDate
    Dim EndDate
    
    StartDate = rst![StartDate1]
    Debug.Print StartDate
    EndDate = rst![EndDate1]
    Debug.Print EndDate
    
    
    
    ' _________________________________________________________________________
    ' Start of Delete table tblReportDates if it exists
    strTableName = "tblReportDates"
    On Error Resume Next
    Set tdf = dbs.TableDefs(strTableName)

    If Err = 3265 Then
        ' Table does not exist.
        '   TableExists = False
    Else
        ' Table exists.
        ' TableExists = True
        DoCmd.DeleteObject acTable, strTableName
    End If
    
    ' Create the empty table for ReportDates
    Set tbl = dbs.CreateTableDef(strTableName)
    Set fld = tbl.CreateField("TheDate", dbDate)

    tbl.Fields.Append fld
    dbs.TableDefs.Append tbl
    dbs.TableDefs.Refresh
    ' End of deleteing tblReportDates if it exists
    ' _________________________________________________________________________
    
    ' Start of generating the Report Dates for the tblReportDates based on the Start & End Dates provided
    Dim NewRepDate
    Dim NewEntryDate
    Dim strsqlInsert
    NewEntryDate = StartDate
    Debug.Print NewEntryDate
    For i = NewEntryDate To EndDate
        'Debug.Print i
        
        NewRepDate = NewEntryDate
        strsqlInsert = "INSERT INTO tblReportDates (TheDate) VALUES (#" & NewRepDate & "#)"
        Debug.Print strsqlInsert
        
        dbs.Execute (strsqlInsert)
        
        NewEntryDate = DateAdd("d", 1, NewRepDate)
    
    Next
    ' End of generating the Report Dates for the tblReportDates
    ' _________________________________________________________________________
    'MsgBox ("Successful")

End Function 

Thanks