Problem with syntax

[GFish1]

Hello,
I am trying to do the following:

- use a text box to type in e.g., smi and have a filtered query run and display all names starting with smi

- I have a list box below the text box that has a full queury of all records. So the text i type in the text ox above needs to be captured and filtered. Below is the filter query which isnt working...I am not sure of the syntax with the LIKE command and single/double quotes/percent/astericks..etc. Any help would be greatly appreciate.

strFilteredList = "SELECT [Employee Info].[Last Name], [Employee Info].[First Name] FROM [Employee Info] WHERE [Employee Info].[Last Name]] LIKE '" & Me.txtSearch.Value & "%" ORDER BY [Employee Info].[Last Name];

 

[Andrzejek]

Do this:

strFilteredList = "SELECT [Employee Info].[Last Name], [Employee Info].[First Name] FROM [Employee Info] WHERE [Employee Info].[Last Name]] LIKE '" & Me.txtSearch.Value & "%" ORDER BY [Employee Info].[Last Name];" 
[blue]
Debug.Print strFilteredList[/blue]

Show us what [blue]BLUE[/blue] line of code displayes in Immediate Window.

BTW, % is a wild card in ORACLE, Access uses * (I think).

Have fun.

---- Andy

 

[Andrzejek]

OK, just a wild guess here:
If you want all names that START with what you have in your txtSearch
[tt]
strFilteredList = "SELECT [Employee Info].[Last Name], [Employee Info].[First Name]
FROM [Employee Info]
WHERE [Employee Info].[Last Name]] LIKE [blue]'" & Me.txtSearch.Value & "*'[/blue]
ORDER BY [Employee Info].[Last Name];" [/tt]

If you want all names that CONTAIN what you have in your txtSearch
[tt]
strFilteredList = "SELECT [Employee Info].[Last Name], [Employee Info].[First Name]
FROM [Employee Info]
WHERE [Employee Info].[Last Name]] LIKE [blue]'[/blue][red]*[/red][blue]" & Me.txtSearch.Value & "*'[/blue]
ORDER BY [Employee Info].[Last Name];" [/tt]

Have fun.

---- Andy

 

[GFish1]

Yes I am using Access, so i used the "*". I put this in:

strFilteredList = "SELECT [Employee Info].[Last Name], [Employee Info].[First Name] FROM [Employee Info] WHERE [Employee Info].[Last Name]] LIKE '" & Me.txtSearch.Value & "*" ORDER BY [Employee Info].[Last Name];"

and get a compile error: expected: end of statement

 

[PHV]

strFilteredList = "SELECT [Last Name],[First Name] FROM [Employee Info]" _
  & " WHERE [Last Name] LIKE '" & Me!txtSearch & "*' ORDER BY [Last Name]"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

You have an extra ] and the quotes are messed up. Try:

strFilteredList = "SELECT [Last Name], [First Name] FROM [Employee Info] " & _
  "WHERE [Last Name] LIKE '" & Me.txtSearch & "*' ORDER BY [Last Name]"

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

I'm feeling a bit like an echo of PH today. At least we are consistent.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

Why do people use spaces in their field names and table names in Access? Wouldn't life be esier if they just do:

strFilteredList = "SELECT Last_Name, First_Name FROM Employee_Info " & _
  "WHERE Last_Name LIKE '" & Me.txtSearch & "*' ORDER BY Last_Name"

???
No [tt][][/tt] needed, users never see them anyway.

Have fun.

---- Andy

 

[GFish1]

I didn't create the database or I would have made numerous changes. I am stuck trying to support it.

I am not getting it to filter....I know a combo box has the feature I need, but might be too involved. I quickly attempted to put in a combo box and the search works, but when I try to select a user name and click a command button to write a record, I guess it doest know that I clicked on and selected that user name and does nothing.

 

[dhookom]

How are you expecting to filter anything?
What are you doing with strFilteredList once it is set?

Duane
Hook'D on Access
MS Access MVP

 

[GFish1]

UPDATE:

I got it working ! I had to modify a function in a module ...must have missed ! Thank you all for your expertise - very commendable !

 

[Andrzejek]

My question about spaces in fields and names was not directed at you (GFish1), it was more 'over all' question because I see it over and over again, and most of it with Access.

And I understand that you are just 'a messanger' and have to work with what you have.

Have fun.

---- Andy