Results 1 to 4 of 4
  1. #1
    DubCap01 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Location
    Dubbo, Australia
    Posts
    104

    Append multiple records using two dates as criteria

    Hi all

    I have an unbound form, and on that I have the following fields:

    RoomNumber '102
    ArriveDate '26-Jan-2015
    DepartDate '29-Jan-2015
    BookingID '45
    GuestID '23



    What I am aiming to do is append a record to TblBookings for each day INCLUDING ArriveDate but excluding DepartDate, by RoomNumber - so in effect using the above dates, it would append 3 records. This in effect stops that room number from being double booked, as there is a record for the date when it IS booked... I have my filters working correctly on the >out part of the results, as if I manually type the values into the table, the rooms get excluded where I want them to be excluded.

    I need help with a For i = .... next i , and the sql statement to get it to append the records

    Anybody want to help me out please?
    Thanks in anticipation

    Pete

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,753
    Try:

    Dim d As Date
    d = Me.StartDate
    Do Until d = Me.EndDate
    CurrentDb.Execute "INSERT INTO tablename(RoomNumber, ResDate, BookingID) VALUES(" & Me.RoomNumber & ", #" & d & "#, " & Me.BookingID & ")"
    d = d + 1
    Loop

    Or

    For i = 0 to EndDate - StartDate - 1
    CurrentDb.Execute "INSERT INTO tablename(RoomNumber, ResDate, BookingID) VALUES(" & Me.RoomNumber & ", #" & Me.StartDate + i & "#, " & Me.BookingID & ")"
    Next
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    DubCap01 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Location
    Dubbo, Australia
    Posts
    104
    Hi June7,

    thanks for the fast and excellent reply. I tried both options, option one worked perfectly, option two didn't

    Does the job, thank you so much for your help!

    Pete

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,753
    Ooops! Missed an & in the second. I have fixed the post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Append Query Saving Multiple Records
    By jewll in forum Queries
    Replies: 5
    Last Post: 12-13-2014, 03:54 AM
  2. Replies: 2
    Last Post: 01-22-2014, 09:38 AM
  3. Replies: 11
    Last Post: 04-30-2012, 07:22 PM
  4. Replies: 5
    Last Post: 12-12-2011, 08:08 AM
  5. Query Criteria with Multiple Dates
    By Jojojo in forum Queries
    Replies: 3
    Last Post: 10-08-2011, 05:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums