IIF Statement & Dlookup combined 1

[BradCollins]

I am not even sure that what I want to do is possible, but I figured after weeks of trying that you guys might be able to stear me in the right direction.

I have a booking system table laid out as such:
tblBookings:
fldRoomID (Number)
fldBookIn (Date)
fldBookOut (Date)

tblRooms:
fldRoomID (Number)
fldRoomName (Text)

Next I created a continuous form (bound to the Rooms table) that has several unbound fields in the form header and in the details shows the list of Rooms.

The unbound fields in the form header are laid out as :
Date() Date()+1 Date()+2 Date()+3 Date+4 X Y

The X & Y are navigation buttons to plus or minus a day from each unbound field allowing the user to scroll through the dates.

Now in the detail I would like to put an equivalent amount of unbound fields that will display "BOOKED" if the room is taken. Whilst I can do this if I bind the form to the bookings table, it gives me all the bookings for one room on a seperate line per booking, I would like one line per room and be able to display whether or not that room is booked.

As all of this data is dynamic, I was wondering if it is possible to lookup the booking dates (both in & out)according to the roomID and then display them on only one line, instead of multiple lines.

I have played around with dlookup and iif statements but just cant get this to work.

Any advice or tips would be most appreciated. Thank you

 

[MajP]

build a query, call it something like "qryRoomBookings"

Lets say your form is called "frmRoomBookings"
it has a field for the room id "txtBxRoomID"
it has a fields for each day: txtBxDay1, txtBxDay2,....
...txtBxDay5

1)I would build a function to make it easier and place in a standard module

Public Function isBooked(intRoomID As Integer, dtmDay As Date) As String
  On Error GoTo errLbl
  Dim strWhere As String
  strWhere = " fldRoomID = " & intRoomID & " AND fldBookIn<=" & sqlDate(dtmDay) & " AND fldBookOut>=" & sqlDate(dtmDay)
  Debug.Print strWhere
  If Not IsNull(DLookup("fldRoomID", "tblBookings", strWhere)) Then
    isBooked = "Booked"
  Else
    isBooked = "Available"
  End If
  Exit Function
errLbl:
  MsgBox Err.Number & Err.Description
End Function

Public Function sqlDate(dtmDay As Date) As String
  sqlDate = "#" & Format(dtmDay, "MM/DD/YYYY") & "#"
End Function

2) Now use your function in a query something like:

SELECT 
  tblRooms.fldRoomID,      
  isBooked([fldRoomID],forms!frmBookings!txtBxDay1) AS 
    day1Booking,
 isBooked([fldRoomID],[forms]![frmBookings]![txtBxDay2]) AS 
    day2Booking
 ...As day5Booking 
FROM tblRooms;

Now use the query as the forms recordsource.

 

[BradCollins]

MajP,

I believe you are a genius or at very least a miracle worker. Thank you very much, that works perfectly and is exactly what i was after.

I have spent weeks trying to get this right. Thank You