SQL in VBA

[stu2005]

Hi Guys

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:

[vba]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[/vba]

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

[zilpher]

Hi

I very rarely do work with Access so this might be a bum steer, but I suspect the issue is with the where clause and the way you are passing the dates in. I suspect the sql engine can't parse datSDate >=RoomBooking.[RBstartDate], but might be better at this:

datSDate >= " & Format(RoomBooking.[RBstartDate],"YYYYMMDD" & " AND "

etc etc (you'd need to change all occurences of this and pass a date format suitable.

This *might* be the answer, but like I say, not great at Access.

HTH

[flavo]

If RBstartDate is stored as a date field in your datebase then you need to wrap your values in #

like this

WHERE (RoomBooking.[RBstartDate] <= #" & datSDate & "# and .......

I generally put the field first before the value, but that's just me.

Dave.