Sorting out the logic

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Ray Pearson
    #1

    Sorting out the logic

    Hi there

    Can anyone help me with the logic that I require please?

    I operate a Bed & Breakfast business and one aspect of the business
    that I have not been able to complete in the programme which I have
    developed is one for when to change the linen etc.

    The criteria is as follows –

    1 night stay – change linen
    2 nights stay – change linen
    3 nights stay – change the linen
    4 nights stay or longer– change the linen every second day.

    The reservation programme has the following to fields –

    RoomNo (Number of each room)
    Begindate (which is the first day of their stay)
    NoOfNights (which obviously determines the number of stay nights and
    the day they are booking out.)

    I would like to create a table which will list the Rooms which need to
    be changed as per any given day in accordance with the criteria
    above.. TIA to anyone who can quickly answer this. Thanks - Ray
    Tags: None
    • Allen Browne
      #2
      Re: Sorting out the logic

      Hi Ray

      This will require you to write some VBA code to open a recordset, loop
      through the dates, and append records. If you are comfortable with writing
      VBA code, this logic should do it.

      You want to change the linen:
      - on the day the booking terminates;
      - each 2nd day of the booking, if it is for 4 nights or more.

      The first one is easy: presumably you have a BeginDate and EndDate fields,
      so it is the same as the EndDate. The basic logic for the in-between dates
      will be something like this:
      Dim dt As Date
      Dim rs As DAO.Recordset
      If DateDiff("d", BeginDate, EndDate) >= 4 Then
      Set rs = dbEngine(0)(0). OpenRecordset(" tblLinenChange" ,
      dbOpenDynaset, dbAppendOnly)
      For dt = BeginDate + 2 To EndDate -1 Step 2
      rs.AddNew
      rs!RoomID = RoomID
      rs!ChangeLinenD ate = dt
      rs.Update
      Next
      rs.Close
      End If
      Set rs = Nothing

      You will also have to deal with removing these bookings (or extending them)
      if the client alters their EndDate.

      --
      Allen Browne - Microsoft MVP. Perth, Western Australia.
      Tips for Access users - http://allenbrowne.com/tips.html
      Reply to group, rather than allenbrowne at mvps dot org.

      "Ray Pearson" <ashfield@parad ise.net.nz> wrote in message
      news:b0ae6018.0 408251744.28f53 555@posting.goo gle.com...[color=blue]
      > Hi there
      >
      > Can anyone help me with the logic that I require please?
      >
      > I operate a Bed & Breakfast business and one aspect of the business
      > that I have not been able to complete in the programme which I have
      > developed is one for when to change the linen etc.
      >
      > The criteria is as follows -
      >
      > 1 night stay - change linen
      > 2 nights stay - change linen
      > 3 nights stay - change the linen
      > 4 nights stay or longer- change the linen every second day.
      >
      > The reservation programme has the following to fields -
      >
      > RoomNo (Number of each room)
      > Begindate (which is the first day of their stay)
      > NoOfNights (which obviously determines the number of stay nights and
      > the day they are booking out.)
      >
      > I would like to create a table which will list the Rooms which need to
      > be changed as per any given day in accordance with the criteria
      > above.. TIA to anyone who can quickly answer this. Thanks - Ray[/color]


        Comment

        Previous template Next
        Working...