Displaying all dates between date range

[Atalla]

Hi all,
I have 2 fields in a table -StartDate and EndDate-. I would like to create a query to display all dates between those 2 dates range. Example, an employee was sick between October 1st and October 15th, thus,
StartDate = 10/01/04
EndDate = 10/15/04
Therefore, only one record exists for that employee.
So, in a query I would like to display all dates, each in a separate record. So, when running the query, I should have 15 consecutive records:
10/01/04
10/02/04
10/03/04
etc..until 10/15/04.

I have a feeling it's an easy one but I can't figure it out.
Appreciate your help.

Thanks.
Atalla

 

[GingerR]

I have a table which contains all calendar dates for several years, which I use for various purposes. This is one of them. It's easy to do in Excel then copy the data to an Access table. Then make a new query, with your existing query and the new 'dates' table. Make a join between the date fields, with the arrow pointing to the date field in your query. Bring the date field from the table into the query grid, and the count from your existing query into the query grid. This will show all dates, regardless of if there is a count or not. You can put the 'between' criteria in this new query as well.

Hope this helps.

 

[sanders720]

Could you do a for loop, such as...

This is not literal. You will need to lookup date formatting and date difference code on google.

for a=enddate to startdate step -1 day
date=dateend-1day
append date to table
next a

 

[Atalla]

Thanks GingerR and sanders720, but the solutions do not really help my case.
I'll try some other solution but if anyone comes up with a solution, please let me know.

Thanks.
Atalla

 

[GingerR]

Why don't these solutions help? From what you say, it's exactly what you need.

 

[Atalla]

May be I didn't explain myself. The employee table contain all kinds of information, e.g., full time or part time, team manager, etc.. and same employee has/could have more than one record and every record contain the StartDate and EndDate of that particular change that occur, e.g., employee changed team manager or promoted from part time to full time and so forth.

So, I realized that even if I succeed in doing it that way, I would have a cumbersome table since I have to do it since the beginning of the year.

Anyhow, appreciate your help again.

Regards,
Hatem

 

[GingerR]

If you mean it's cumbersome to have a table that lists all dates for a year or multiple years...it's not really cumbersome at all, and once you have it built, it's done and you can use it forever. In Excel, open a new file. Type in 1/1/04. In the cell underneath that, put =A1+1. then drag that forumla down as far as you wish. Then copy all those cells, open a new Access table in Datasheet view. Ctrl+A (select all) and hit the DELETE key to get rid of the 30 blank records that show up in a new table. Click in the upper-left-most cell and hit ctrl+v (paste). close your table, re-name the field to be CalendarDate. All done. This will work well for you no matter what dates you have...you just put in "between StartDate and EndDate", etc...

 

[raskew]

Creating the table using VBA is easily done. Copy/paste the code to a new module then, in the debug window type
Call CalendarMake(2005, 2005) <enter>
In a couple of seconds the calendar table will be displayed.
To add or modify fields in the table, it's only necessary to modify this one line of code.

    strSQL = "CREATE TABLE " & tName & "(DateID counter, MyDate date, " _
           & "Remarks text(50));"

HTH - Bob

Public Sub CalendarMake(pSYear As Integer, pEYear As Integer)
'************************************************************
'Purpose:   Create table of calendar days
'           for specified, contiguous years.

'Coded by:  raskew

'Inputs:    from debug (immediate) window:
'           (1) Call CalendarMake(1999, 2000) <enter>
'           (2) Call CalendarMake(2004, 2004) <enter>
'Output:    (1) table: tbl1999_2000
'           (2) table: tbl2004

'WARNING:   As written (see Step 1), this procedure will
'           delete an existing table of the same name!!
'           If this could be an issue, you must rewrite
'           Step 1 to provide warning of impending doom!!
'************************************************************

Dim db       As DATABASE
Dim rs       As Recordset
Dim td       As TableDef
Dim n        As Double
Dim fld      As Field
Dim intEnd   As Integer
Dim intStart As Integer
Dim strSQL   As String
Dim tName    As String

    'allow for and correct reversed start/end dates
    intStart = IIf(pSYear <= pEYear, pSYear, pEYear)
    intEnd = IIf(pEYear >= pSYear, pEYear, pSYear)
    
    'create table name
    tName = "tbl" & Format(intStart) & IIf(intEnd > intStart, "_" & Format(intEnd), "")
    
    '(1) Does table tName exist?  If so, delete it;
    Set db = CurrentDb
    
    On Error Resume Next
    db.Execute "DROP TABLE " & tName & ";"
    
    '(2) Create/recreate table.  You should add/modify fields as required.
    '
    strSQL = "CREATE TABLE " & tName & "(DateID counter, MyDate date, " _
           & "Remarks text(50));"
    db.Execute strSQL
    '
    Set td = db.TableDefs(tName)
    '
    '(3) Add autonumber field
    Set fld = td.Fields("DateID")
    fld.Attributes = dbAutoIncrField
    
    '(4) Make DateID the key field
    db.Execute "CREATE INDEX NewIndex ON " & tName & " (DateID) WITH PRIMARY;"

    '(5) Populate the table
    Set rs = db.OpenRecordset(tName)
    For n = CDbl(DateSerial(intStart, 1, 1)) To CDbl(DateSerial(intEnd, 12, 31))
       With rs
          .AddNew
          !MyDate = n
          .Update
       End With
    Next n
    
    '(6) Clean-up
    rs.Close
    db.Close
    Set db = Nothing
    
    '(7) display new table
    docmd.OpenTable tName, acViewNormal

End Sub

 

[josheppi]

Ginger:

I am having the same issue and wanted to ask you for more detailed instructions. I also have several fields and hundreds of records for which I have to show the fields and the dates between the start and end date, but not including the end date. Thanks.

josheppi