Search form

RedTrac

New member
Local time
Today, 11:15
Joined
May 14, 2007
Messages
4
Im creating a search form for my database and written the following SQL for it,

SELECT Rooms.Room_id, Rooms.Sleeps, Rooms.Type, Rooms.Phone Number, Rooms.Price, Bookings.Date
FROM Rooms, Bookings
WHERE Date="txtdate", Type="cbtype";

I have two fields one being "txtdate" and one being a combo box "cbtype".

I want the user to be able to type in a date and choose a room type from the combo box and upon clicking the search button results of all the empty rooms are displayed in a listbox.

anothing thing it should look up the bookings table and checks the contents of txtdate on the search form with the contents of the arrival date field on the bookings table and if the users entered date is the same as an existing booking it will display booked in the list box.

Im sorry if thats confusing If you dont quite get what im on about i'll try and explain it in a differnt way.

Thanks,
RT
 
Try

WHERE Date=Forms!FormName.txtdate AND Type = Forms!FormName.cbtype
 
Red,

Code:
SQL = "SELECT Rooms.Room_id, Rooms.Sleeps, Rooms.Type, Rooms.Phone Number, Rooms.Price, Bookings.Date " & _
      "FROM Rooms, Bookings " & _
      "WHERE Date = #" & Me.txtdate & "# And Type = '" & Me.cbtype & "'"
Me.YourListBox.RowSource = SQL
Me.YourListBox.Requery

BUT, you could add an Order By clause
AND, your results may not be as expected because you have a cartesian join.

You're gonna get a lot of records!

Wayne
 
Red,

Forgot to add, Date is reserved for use by Access.

It will lead to inexplicable behaviour.

Wayne
 
Thank you so much for your help.

your results may not be as expected because you have a cartesian join.

You're gonna get a lot of records!

This should'nt be a problem as I dont have too many records.
 
Red,

Glad to help ... keep me posted as to your success.

Wayne
 
I tried out the following code and after lots of trying out other ways, it is still not working :(

When I click on the search button all the records disapear.

---

Private Sub btnsearch_Click()
SQL = "SELECT Rooms.Room_id, Rooms.Sleeps, Rooms.Type, Rooms.Phone Number, Rooms.Price, Bookings.Date " & _
"FROM Rooms, Bookings " & _
"WHERE Date = #" & Me.txtdate & "# And Type = '" & Me.cbtype & "'"
Me.lbsearch.RowSource = SQL
Me.lbsearch.Requery

End Sub
 

Attachments

  • roomsearch2.png
    roomsearch2.png
    15.2 KB · Views: 116
Last edited:
Hi,

"Date" is a reserved word in Access. Rename it if it is possible else use a square bracket like [Date] = #" & Me.txtdate & "#.

PS. The correct one is [Date] = #" & Me.txtdate & "#" . seems like you missing one more qoute.

I tried out the following code and after lots of trying out other ways, it is still not working :(

When I click on the search button all the records disapear.

---

Private Sub btnsearch_Click()
SQL = "SELECT Rooms.Room_id, Rooms.Sleeps, Rooms.Type, Rooms.Phone Number, Rooms.Price, Bookings.Date " & _
"FROM Rooms, Bookings " & _
"WHERE Date = #" & Me.txtdate & "# And Type = '" & Me.cbtype & "'"
Me.lbsearch.RowSource = SQL
Me.lbsearch.Requery

End Sub
 

Users who are viewing this thread

Back
Top Bottom