Solved Listbox RowSource

[mafhobb]

I have a listbox (SearchResults) on the form "FindGuest" that uses the following rowsource:

SELECT tblReservations.ID, tblReservations.PropertyName, tblReservations.GuestName, tblReservations.CheckInDate, tblReservations.CheckOutDate, tblReservations.ReservationDate, tblReservations.Status, tblResOrigin.ResOrigin
FROM tblResOrigin INNER JOIN tblReservations ON tblResOrigin.ID = tblReservations.ReservationSource
WHERE (((tblReservations.GuestName) Like "*" & [forms]![frmPropertyHistory]![txtFindGuest] & "*"))
ORDER BY tblReservations.CheckInDate;

This pulls the search string (txtFindGuest) from the form that opens this one.

Elsewhere in this form I have a textbox (txtFind Guest) and a button (cmdFindGuest). The idea is to be able to perform more searches directly from this form after the initial one.
I have the following code in the button's on click event:

Me.SearchResults.RowSource = "SELECT tblReservations.ID, tblReservations.PropertyName, tblReservations.GuestName, tblReservations.CheckInDate, tblReservations.CheckOutDate, tblReservations.ReservationDate, tblReservations.Status, tblResOrigin.ResOrigin," _
& "FROM tblResOrigin INNER JOIN tblReservations ON tblResOrigin.ID = tblReservations.ReservationSource," _
& "WHERE (((tblReservations.GuestName) Like " * " & [Me]![txtFindGuest] & " * "))," _
& "ORDER BY tblReservations.CheckInDate;"

Unfortunately I am receiving a "Run-time error 13. Type Mismatch"
The only thing that has changed in the origin of the seach string, but it is still a string.
Why is this happening?

 

[Gasman]

Strings need single quotes surrounding them, unless the string contains a single quote. Then it is triple double quotes or Chr().
Besides that sql is not identical in syntax?

 

[Minty]

Debugging 101 : You need to put your string into a variable and Debug.Print it and the error will be fairly obvious.

You need to add single quotes around your search string, but that won't be your only problem.
The debug will help you.

 

[mafhobb]

MsgBox Me.txtFindGuest
MsgBox Forms!frmFindGuest!txtFindGuest.Value
Me.SearchResults.RowSource = "SELECT tblReservations.ID, tblReservations.PropertyName, tblReservations.GuestName, tblReservations.CheckInDate, tblReservations.CheckOutDate, tblReservations.ReservationDate, tblReservations.Status, tblResOrigin.ResOrigin FROM tblResOrigin INNER JOIN tblReservations ON tblResOrigin.ID = tblReservations.ReservationSource WHERE (((tblReservations.GuestName) Like ' * ' & 'Forms!frmFindGuest!txtFindGuest.value' & '*')) ORDER BY tblReservations.CheckInDate"

Still struggling. No error now, but no results either
The debig.print shows "False"

 

[Gasman]

So you have not done what has been advised/asked?
Put all the select into a string variable and debug.print it.
Then if you still cannnot see the errors, you can copy and paste back here.

 

[Minty]

The Debug of your concatenated SQL String would show you the problem. I really don't understand not doing it.

It would show you some unwanted spaces in your criteria.

 

[mafhobb]

So you have not done what has been advised/asked?
Put all the select into a string variable and debug.print it.
Then if you still cannnot see the errors, you can copy and paste back here.

I think that I did, but I am not sure of the process/syntax.
I basically did this:

Debug.Print a = "SELECT tblReservations.ID, tblReservations.PropertyName, tblReservations.GuestName, tblReservations.CheckInDate, tblReservations.CheckOutDate, tblReservations.ReservationDate, tblReservations.Status, tblResOrigin.ResOrigin FROM tblResOrigin INNER JOIN tblReservations ON tblResOrigin.ID = tblReservations.ReservationSource WHERE (((tblReservations.GuestName) Like  ' * ' & 'Forms!frmFindGuest!txtFindGuest.value' & ' * ')) ORDER BY tblReservations.CheckInDate"

On the inmediate window I see "false"

 

[Gasman]

StrSql = "SELECT ......"
DEBUG.PRINT StrSql

See link in my signature for basic debugging, which you have to do, if you ever want to write code that works as intended.

 

[arnelgp]

you try:

Me.SearchResults.RowSource = _
"SELECT tblReservations.ID, tblReservations.PropertyName, tblReservations.GuestName, " & _
"tblReservations.CheckInDate, tblReservations.CheckOutDate, " & _
"tblReservations.ReservationDate, tblReservations.Status, tblResOrigin.ResOrigin " & _
"FROM tblResOrigin INNER JOIN tblReservations " & _
"ON tblResOrigin.ID = tblReservations.ReservationSource " & _
"WHERE (((tblReservations.GuestName) Like '*" & Forms!frmFindGuest!txtFindGuest & "*')) ORDER BY tblReservations.CheckInDate"

 

[mafhobb]

Thank you everyone.
Gasman: Looking at the debuggin videos.
arnelgp: From my last try, it was the single and double quotations and their location in the "Like ....." that was messed up. Thanks!