I have created a hotel booking system programme. However, I am puzzled as to how when the booking button is clicked it can search a database and find the best suited room (RoomType) and make sure that it is available (Date of Departure < system.date).

This data is held in two tables (Access):

Booking:
BookingID (Primary Key)
CustomerID (Foreign Key, Table:Customer)
Date of Arrival (Date)
Date of Departure (Date)
RoomID (Foreign Key,Table:Room)

Room:
RoomID (Primary Key)
RoomType (Text)
RoomPrice (Currency)
Floor (Number)

This has all got to be done within a single button click. Any ideas as to how I can place this into my vb code?

I have a rough query for picking a room that is best suited:

I have been trying for a while to get it to select a single matching record as there is a high chance that this query will return multiple records but im unsure on how to deal with that.

Any help would be hugely appreciated.

That is definitely not a valid MySQL Syntax (actually the & thing is not valid in any SQL I know)

This looks like SQL Server, you should post the question there.

And post the real SQL Statement not your VB code (print the real SQL statement and most probably you'll se the error yourself)

I dont understand what you mean? I have not posted any visual basic coding at all.. This is the SQL command that I am trying to create an place in VB.NET

Take a look at LIMIT to specify that you only want a single row returned. Something like: LIMIT 0,1. Also, I would move "AND Booking.[Date of Departure] < Now()" into the ON clause just for clarity sake.
Dave

Sure you have: LIKE '" & Room_TypeTextbox.Text & "'" is not valid SQL, it's VB (or some other programming language)

and AND Booking.[Date of Departure] < Now() is not valid syntax for MySQL, it's the wrong forum.