Results 1 to 4 of 4
  1. #1
    dlowrey is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2020
    Posts
    2

    Delete Table Row if field/column text contains substring

    I am new to Access and I am stuck.
    I want to iterate through an entire table deleting all rows in which a Column text contains the substring "LLC".
    I have been trying to use INSTR() but I can not figure out how to make it work with something like:
    strSQL = "Delete * From [TABLENAME] WHERE [COLUMNNAME] ...contains the substring...
    DoCmd.RunSQL strSQL



    Thank you for your kind assistance.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Is the "LLC" at the start of the field, at the end, or somewhere in the middle?

    Always prudent to do a read/Select to identify the records you plan to delete --rather than messing things up and deleting the wrong records.

    create a SELECT query in SQL view; paste this code into the window
    Code:
    SELECT * FROM [TABLENAME] WHERE [COLUMNNAME] like "*" & "LLC" & "*"

  3. #3
    dlowrey is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2020
    Posts
    2
    Thank you!

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You are welcome. Good luck with your project.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 12-29-2017, 04:06 PM
  2. Replies: 17
    Last Post: 05-01-2015, 09:30 PM
  3. getting substring of a column value
    By funandlearning in forum Access
    Replies: 3
    Last Post: 06-19-2012, 09:49 AM
  4. Replies: 3
    Last Post: 03-14-2012, 06:48 AM
  5. Can't delete a column (field)
    By jlmnjem in forum Access
    Replies: 2
    Last Post: 09-20-2010, 12:02 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums