Sorting out the logic

[Ray Pearson]

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

[Allen Browne]

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!ChangeLinenDate = 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" <as******@paradise.net.nz> wrote in message
news:b0**************************@posting.google.c om...

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