We help IT Professionals succeed at work.

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

127 Views
Last Modified: 2018-08-24
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

Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
Hamed NasrRetired IT Professional
CERTIFIED EXPERT

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

to:
strSQL = strSQL & " WHERE " & instr(Tbl_V0.WBS,'.',1) & " >0 "
CERTIFIED EXPERT
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..

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
CERTIFIED EXPERT
Top Expert 2016

Commented:
have you tried CharIndex? Can you show your actual query?
CERTIFIED EXPERT
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
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks to all !

Pete
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
you're welcome.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
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
Empower Your Career
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

Ask ANY Question

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

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.