Search form

[RedTrac]

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

 

[pbaldy]

Try

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

 

[WayneRyan]

Red,

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

 

[WayneRyan]

Red,

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

It will lead to inexplicable behaviour.

Wayne

 

[RedTrac]

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.

 

[WayneRyan]

Red,

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

Wayne

 

[RedTrac]

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

 

[unclejoe]

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