Avatar of Peter Groves
Peter Groves
 asked on

We want to specify a where condition using instr() in an SQL string.

We want to specify a where condition using instr() in an SQL string.

 strSQL = ""
        strSQL = strSQL & "UPDATE tbl_V0"     'Nom de la table où faire mise a jour
        strSQL = strSQL & " SET V0='" & UncheckboxTblV0 & "'"
        strSQL = strSQL & " WHERE instr(Tbl_V0.WBS,'.',1) >0 " 
       
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = cn
        .CommandText = strSQL
        .CommandType = adCmdText
        Set rs = .Execute
    End With

We have a table with a checkbox field that we want to modify if another field in the same table contains a period!    Like   abcdefg.xxx

Pete
Microsoft AccessVBASQL

Avatar of undefined
Last Comment
PatHartman

8/22/2022 - Mon
Éric Moreau

Hamed Nasr

Modify:
strSQL = strSQL & " WHERE instr(Tbl_V0.WBS,'.',1) >0 "

to:
strSQL = strSQL & " WHERE " & instr(Tbl_V0.WBS,'.',1) & " >0 "
PatHartman

If your BE is SQL server, you do NOT want to use VBA functions in the where clause.  If there is no  T-SQL equivalent to Instr() then that will force Access to bring the entire table local to the PC running the query and apply the where clause there.  So, if your table is large and the query takes too long to run, you should switch to using

strSQL = strSQL & " WHERE(Tbl_V0.WBS Like "*.*" " 

If you need a pass through query, the wild card would be different.  I don't remember what T-SQL uses but it might be % instead of *  but in that case, it would be better to use CharIndx as suggested by Eric, But CharIndex will ONLY work if you use a pass through query since it is not valid in Jet/ACE SQL..
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Peter Groves

ASKER
that gives me a missing expression error!  

The rest works fine as we run the code fine without this part added!

Thanks
Pete
Éric Moreau

have you tried CharIndex? Can you show your actual query?
PatHartman

that gives me a missing expression error!  
What is "that".  You need to post the code you are using.

Eric, this is an Access application.  CharIndex will only work if Peter uses a pass through query which MUST use T-SQL syntax which he may not know.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Peter Groves

ASKER
We tried charindex aws per the microsoft site , with no luck.

Its on a colleques computer a will only see him tomorrow!

Pete
Peter Groves

ASKER
The code is what you posted Pat,  and the missing expression is because the error code is in french!

Erreur de compliation     Attendu: expression
which  translates to Compilor error    Expected expression.

Pete
ASKER CERTIFIED SOLUTION
PatHartman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Peter Groves

ASKER
Thanks to all !

Pete
Your help has saved me hundreds of hours of internet surfing.
fblack61
PatHartman

you're welcome.