stu2005
02-15-2005, 01:45 PM
Hi Guys :hi:
I am a newbie here so hopefully i have placed this post in the correct place!
I am building a module in VBA to search for available rooms in a booking system.
So far the logic goes like this:
First draw up a list (call it list X) of all rooms that meet the capacity requirements
Then draw up a list (call it list Y) of unavailable rooms for the required dates.
If any room on list X equal one of list Y, remove the unavailable room from list X leaving only list X full of available rooms.
Heres where i hit problems. I have an SQL statement that creates list Y but i cant get it to work in VBA, any ideas?
Heres the code below for creating List Y:
Public Sub sqlRoomBook()
Dim db As Database
Dim rec As Recordset
Dim strSQL As String
Dim strRoomID As String
Dim datSDate As Date
Dim datEDate As Date
datSDate = InputBox("Enter Start Date", , Default)
datEDate = InputBox("Enter End Date", , Default)
Set db = CurrentDb()
strSQL = "SELECT [RBroomID] FROM RoomBooking WHERE (datSDate >=RoomBooking.[RBstartDate] AND " _
& "datSDate <= RoomBooking.[RBendDate])OR (datEDate >= RoomBooking.[RBstartDate] AND " _
& "datEDate <= RoomBooking.[RBendDate])"
Set rec = db.OpenRecordset(strSQL)
Do Until rec.EOF
strRoomID = strRoomID & Chr(13) & rec!RBbookingID
rec.MoveNext
Loop
MsgBox strRoomID
end sub
If anyone knows of any good articles online regarding SQL in VBA it would be great if you could point me in the direction.
Big thanks in advance for any help!
Stu
I am a newbie here so hopefully i have placed this post in the correct place!
I am building a module in VBA to search for available rooms in a booking system.
So far the logic goes like this:
First draw up a list (call it list X) of all rooms that meet the capacity requirements
Then draw up a list (call it list Y) of unavailable rooms for the required dates.
If any room on list X equal one of list Y, remove the unavailable room from list X leaving only list X full of available rooms.
Heres where i hit problems. I have an SQL statement that creates list Y but i cant get it to work in VBA, any ideas?
Heres the code below for creating List Y:
Public Sub sqlRoomBook()
Dim db As Database
Dim rec As Recordset
Dim strSQL As String
Dim strRoomID As String
Dim datSDate As Date
Dim datEDate As Date
datSDate = InputBox("Enter Start Date", , Default)
datEDate = InputBox("Enter End Date", , Default)
Set db = CurrentDb()
strSQL = "SELECT [RBroomID] FROM RoomBooking WHERE (datSDate >=RoomBooking.[RBstartDate] AND " _
& "datSDate <= RoomBooking.[RBendDate])OR (datEDate >= RoomBooking.[RBstartDate] AND " _
& "datEDate <= RoomBooking.[RBendDate])"
Set rec = db.OpenRecordset(strSQL)
Do Until rec.EOF
strRoomID = strRoomID & Chr(13) & rec!RBbookingID
rec.MoveNext
Loop
MsgBox strRoomID
end sub
If anyone knows of any good articles online regarding SQL in VBA it would be great if you could point me in the direction.
Big thanks in advance for any help!
Stu