VBA SQL Query with partial matching

R

rc51wv

I got the VBA query set up so that it works with you selecting a field name
from a combo box and typing in the data to search for in the text box next to
the combo in the form.

Now I need to make it so partial matches show up when they search.

Example - when using the find function, you can choose to match the
information entered to anything that has that information in it.

So searching for green would return greenhouse, green house, greenie, etc...

Is there an SQL command I can add in do this or am I going to have to do it
another way?

-TIA
 
M

Marshall Barton

rc51wv said:
I got the VBA query set up so that it works with you selecting a field name
from a combo box and typing in the data to search for in the text box next to
the combo in the form.

Now I need to make it so partial matches show up when they search.

Example - when using the find function, you can choose to match the
information entered to anything that has that information in it.

So searching for green would return greenhouse, green house, greenie, etc...

Is there an SQL command I can add in do this or am I going to have to do it
another way?
Click to expand...


Use a criteria with the Like operator instead the =
operator. The code in your procedure would be something
like:

strSQL = strSQL & "WHERE [" & Me.combo & "] LIKE """ & _
Me.textbox & "*"""
 
R

Randy Wayne

Related to Marshall's reply, I have a question. In T-SQL, when do you use
the wildcard % instead of *? Also, what about the single quotes inside the
double quotes.

I would have written the statement like :

strSQL = strSQL & "WHERE [" & Me.combo & "] LIKE "'" & _
Me.textbox & "%"'"

Is there a difference?
 
M

Marshall Barton

Randy said:
Related to Marshall's reply, I have a question. In T-SQL, when do you use
the wildcard % instead of *? Also, what about the single quotes inside the
double quotes.

I would have written the statement like :

strSQL = strSQL & "WHERE [" & Me.combo & "] LIKE "'" & _
Me.textbox & "%"'"

Is there a difference?
Click to expand...


Your quotes are not balanced. I know from nothing when it
comes to TSQL, but my intuition thinks it should more like
this:

strSQL = strSQL & "WHERE [" & Me.combo & "] LIKE '" & _
Me.textbox & "%' "
 
J

John Spencer

Marshall,
For someone that said "I know from nothing when it > comes to TSQL", you hit
the code right on the money.

In TSQL the wild card is %. So you always use the % instead of the *.

Marshall Barton said:
Randy said:
Related to Marshall's reply, I have a question. In T-SQL, when do you use
the wildcard % instead of *? Also, what about the single quotes inside
the
double quotes.

I would have written the statement like :

strSQL = strSQL & "WHERE [" & Me.combo & "] LIKE "'" & _
Me.textbox & "%"'"

Is there a difference?
Click to expand...


Your quotes are not balanced. I know from nothing when it
comes to TSQL, but my intuition thinks it should more like
this:

strSQL = strSQL & "WHERE [" & Me.combo & "] LIKE '" & _
Me.textbox & "%' "
Click to expand...
 
Top