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

Peter Groves
Peter Groves used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
Hamed NasrRetired IT Professional

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

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

Commented:
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..
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
that gives me a missing expression error!  

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

Thanks
Pete
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
have you tried CharIndex? Can you show your actual query?
Distinguished Expert 2017

Commented:
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.

Author

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

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

Pete

Author

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
Distinguished Expert 2017
Commented:
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.

Author

Commented:
Thanks to all !

Pete
Distinguished Expert 2017

Commented:
you're welcome.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial