loop through dates

[lizba]

Hi there
I have a form where someone books a room (Room 1) for x number of nights - from [DateIn] = 2010/04/28 to [DateOut] = 2010/05/02. I want to load this info to a table similar to this:

Room Date Customer
Room1 2010/04/28 Mr Jones
Room1 2010/04/29 Mr Jones
Room1 2010/04/30 Mr Jones
Room1 2010/05/01 Mr Jones

So I need to create a loop that will add a record for each date between DateIn and DateOut-1.

Can someone help me???
Lizba

[ADezii]

Hi there
I have a form where someone books a room (Room 1) for x number of nights - from [DateIn] = 2010/04/28 to [DateOut] = 2010/05/02. I want to load this info to a table similar to this:

Room Date Customer
Room1 2010/04/28 Mr Jones
Room1 2010/04/29 Mr Jones
Room1 2010/04/30 Mr Jones
Room1 2010/05/01 Mr Jones

So I need to create a loop that will add a record for each date between DateIn and DateOut-1.

Can someone help me???
Lizba

Just subscribing, will return later on with a solution.

[lizba]

That will be great, Thanks

[ADezii]

1. Let's assume that you have a Table with the following Fields:
   • Room - {TEXT}
   • DateIn - {DATE/TIME}
   • DateOut - {DATE/TIME}
   • Customer - {TEXT}
2. Create a Table named tblBookings 2 with the following Fields:
   • Room - {TEXT}
   • Date - {DATE/TIME}
   • Customer - {TEXT}
3. Execute the following code to produce the results that you have requested:
   

   Dim MyDB As DAO.Database
   Dim rstBookings As DAO.Recordset
   Dim rstBk2 As DAO.Recordset
   Dim intDayCtr As Integer
   
   Set MyDB = CurrentDb
   Set rstBookings = MyDB.OpenRecordset("tblBookings1", dbOpenForwardOnly)
   Set rstBk2 = MyDB.OpenRecordset("tblBookings2", dbOpenDynaset)
   
   'Clear all Records in tblBookings2
   CurrentDb.Execute "DELETE * FROM tblBookings2;", dbFailOnError
   
   'Loop thru all the Booking Records
   With rstBookings
     Do While Not .EOF
       'Break down the Date Range into Linear Dates and ADD to tblBookings2
       For intDayCtr = 0 To DateDiff("d", ![DateIn], ![DateOut]) - 1
         rstBk2.AddNew
           rstBk2![Room] = ![Room]
           rstBk2![Date] = DateAdd("d", intDayCtr, ![DateIn])
           rstBk2![Customer] = ![Customer]
         rstBk2.Update
       Next
         .MoveNext
     Loop
   End With
   
   rstBk2.Close
   rstBookings.Close
   Set rstBookings = Nothing
   Set rstBk2 = Nothing
   
   DoCmd.OpenTable "tblBookings2", acViewNormal, acReadOnly
   DoCmd.Maximize

4. Download the Test Database for this Thread which I made available to you as an Attachment.
5. Any questions on anything, feel free to ask.

[lizba]

Thank you so so so much. It works perfectly!!

[ADezii]

Thank you so so so much. It works perfectly!!

Glad it worked out for you.