We help IT Professionals succeed at work.
Get Started

Creating sql string for vba query with wildcard characters

Fordraiders
Fordraiders asked
on
199 Views
Last Modified: 2016-02-16
Excel 2010 vba

I'm taking a text string from a cell
Splitting that into an array.

The code works fine UNLESS i only have one Token.  i.e.   FLAPPER  OR  VALVE

I'm creating the end of the "WHERE" clause in my sql statement.

for ONE word,   two word and/or  three word strings.


    lk = "Like"
    lm = " WwgCore.RICHTEXT "
    
    cD = Trim(cD)
    cD = Replace(cD, "  ", " ")
    cD = Replace(cD, ",", " ")
    cD = Replace(cD, "#", "")
    cD = Replace(cD, """", "")

    ' how many items are in the cell ?  build an array
            
    nF1 = cD
            
    cArrSql = Split(nF1, " ")
            
    ' search through the cell requests tokens.
    For x = LBound(cArrSql) To UBound(cArrSql)
        If x = UBound(cArrSql) Then
            nF1 = Trim(nF1 & " " & Chr(34) & "%" & cArrSql(x) & "%" & Chr(34))
               
        Else
            If x = 0 Then
                nF1 = Trim(Chr(34) & "%" & cArrSql(x) & "%" & Chr(34) & " AND" & lm & lk & " ")
            
            Else
                nF1 = Trim(nF1 & " " & Chr(34) & "%" & cArrSql(x) & "%" & Chr(34) & " AND" & lm & lk & " ")
            
            End If
        End If
    Next

    rng2 = nf1

Open in new window


The end of my WHERE clause is:
strsql = strsql & "WHERE WwgCore.RICHTEXT LIKE " & rng2 & ";"

for 2 tokens the string looks like this :
rng2 =   "%FLAPPER%" AND WwgCore.RICHTEXT Like "%VALVE%"  and the code works fine

ultimately nf1 for One Word token should be passed as  "%FLAPPER%"

per above example:

But does not work for ONE word tokens

Thanks
fordraiders
Comment
Watch Question
Test your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Distinguished Expert 2020
Commented:
This problem has been solved!
Unlock 2 Answers and 8 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE