VBA SQL Query with partial matching

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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 & "*"""
 
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?
 
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 & "%' "
 
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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Searching for partial match on a referenced table? 2
Access Partially match 2 lists 1
Index with partial text 1
With F1 value, look at A, if match or partial match return B 5
Query record in SQL 4
SQL statement in form text control 2
IF formula, Partial word, SUM function as result 1
Multi Column Combo box Not finding correct record in Form 2

Back
Top