Syntax for UPDATE...LIKE in vb module

Posted on 2008-11-12
Last Modified: 2012-05-05
I'm trying to clean up some data in Access 2000, so I want to capture anything 'like' a string of text and update it.  There are going to be 30 variations of below so I'm storing them in a module rather than writing a bunch of separate queries; I just need to know what's wrong with WHERE statement that gives me the syntax error:

Public Sub doUpdates()

Dim mysql As String

mysql = "UPDATE tblTerritory_AnalysisFINAL " & _

"SET tblTerritory_AnalysisFINAL.Terr_Name = 'Health Insurance'" & _

"WHERE tblTerritory_AnalysisFINAL.Terr_Name= LIKE '*Health Insurance*'"

DoCmd.RunSQL mysql

End Sub

Open in new window

Question by:jimshock
    LVL 119

    Accepted Solution


    Public Sub doUpdates()
    Dim mysql As String
    mysql = "UPDATE tblTerritory_AnalysisFINAL " & _
    " SET tblTerritory_AnalysisFINAL.Terr_Name = 'Health Insurance'" & _
    " WHERE tblTerritory_AnalysisFINAL.Terr_Name LIKE '*Health Insurance*'"
    DoCmd.RunSQL mysql
    End Sub

    Author Closing Comment

    Awesome!  Fast and dead-on.  Thanks so much, capricorn1 !

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now