search any part of field and show on listbox

[Zanetti via AccessMonster.com]

Hi o all
I have problem with search on form. I try to create one text box and list box
on the form and when i type words , list box automatically show all result
with that word. I try to put selected code on change event on text box
Me.List0.RowSource = " SELECT nazivartikla FROM Artikli WHERE nazivartikla
Like " * " '" & Forms!proba!Text1 & "'" * ""

Always when i try to change he report error data type mismatch.
Thanks

 

[Allen Browne]

Break it down so you can see you SQL statement in the Immediate Window
(Ctrl+G):

Dim strSql As String
strSql = " SELECT nazivartikla FROM Artikli WHERE nazivartikla Like ""*" &
Forms!proba!Text1 & "*"";"
debug.print strSql
Me.List0.RowSource = strSql

Explanation of the quotes:
http://allenbrowne.com/casu-17.html

 

[Douglas J. Steele]

Your quotes are incorrect.

Me.List0.RowSource = " SELECT nazivartikla FROM Artikli " & _
"WHERE nazivartikla Like " ' * " & Forms!proba!Text1 & " * ' "

Of course, the spaces around the quotes shouldn't be there:

Me.List0.RowSource = " SELECT nazivartikla FROM Artikli " & _
"WHERE nazivartikla Like "'*" & Forms!proba!Text1 & "*'"

Note that if the value in Text1 can contain apostrophes, use

Replace(Forms!proba!Text1, " ' ", " ' ' ")

instead of just

Forms!proba!Text1

Again, remove the spaces so that it's simply Replace(Forms!proba!Text1, "'",
"''")

 

[Zanetti via AccessMonster.com]

thanks for fast replay,
I see my mistakes but i have another problem because i put code on change
event on text 1 . I try to make that list box show all results which contain
word from text1 every time automatically when i change text1.
Help plz

 

[Allen Browne]

Use the Text property of the text box:

strSql = " SELECT nazivartikla FROM Artikli WHERE nazivartikla Like ""*" &
Forms!proba!Text1.Text & "*"";"

 

[Zanetti via AccessMonster.com]

Hio
query = "SELECT nazivartikla FROM Artikli WHERE nazivartikla Like " * " ' " &
Me.TEXT1 & "' " * ""
I all time i get error type mismatch.

 

[Douglas J. Steele]

Your quotes are wrong.

query = "SELECT nazivartikla FROM Artikli WHERE nazivartikla Like " ' * " &
_
Me.TEXT1 & " ' * "

(and, of course, the spaces shown inside the quotes shouldn't be there)

If there's a chance that Me.TEXT1 will contain apostrophes, use

query = "SELECT nazivartikla FROM Artikli WHERE nazivartikla Like " ' * " &
_
Replace(Me.TEXT1, " ' ", " ' ' ") & " ' * "