Link to home
Start Free TrialLog in
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
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

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

to:
strSQL = strSQL & " WHERE " & instr(Tbl_V0.WBS,'.',1) & " >0 "
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..
Avatar of Peter Groves
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
have you tried CharIndex? Can you show your actual query?
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.
We tried charindex aws per the microsoft site , with no luck.

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

Pete
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
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks to all !

Pete
you're welcome.