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
Peter GrovesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
0
hnasrCommented:
Modify:
strSQL = strSQL & " WHERE instr(Tbl_V0.WBS,'.',1) >0 "

to:
strSQL = strSQL & " WHERE " & instr(Tbl_V0.WBS,'.',1) & " >0 "
0
PatHartmanCommented:
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..
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Peter GrovesAuthor Commented:
that gives me a missing expression error!  

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

Thanks
Pete
0
Éric MoreauSenior .Net ConsultantCommented:
have you tried CharIndex? Can you show your actual query?
0
PatHartmanCommented:
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.
0
Peter GrovesAuthor Commented:
We tried charindex aws per the microsoft site , with no luck.

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

Pete
0
Peter GrovesAuthor Commented:
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
0
PatHartmanCommented:
1. You didn't post the query.
2. If you use CharIndex, you MUST be using a pass-through query.  Are you?

Sorry, I copied and pasted but didn't finish the correction.

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

Put a stop in the code after the string is created and print the string to the immediate window.  That will make it easier to see the syntax error.  You could also take that string and paste it into the SQL view of the QBE and try to run it.  You will very likely get a better error message but it will probably still be in French which is still Greeks to me  The above change will fix the compile error.  It was missing a closing ")".  So, since the "(" wasn't ever needed, I just eliminated it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Peter GrovesAuthor Commented:
Thanks to all !

Pete
0
PatHartmanCommented:
you're welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.