dcsimg
Results 1 to 4 of 4

Thread: DELETE with LIKE condition

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Sep 2012
    Posts
    387

    DELETE with LIKE condition

    There's a MS Access table which look like following.

    column1 column2
    ---------------------------------
    prod1 food
    prod2 food
    prod3 apparel
    serv1 insurance
    serv2 insurance
    serv3 nailshop
    ---------------------------------

    I want to delete record of which column1 has "proc" and column2 is "food"
    If I run SQL in the MS Access's SQL view with following SQL statement, corresponding records are deleted.

    Code:
    DELETE FROM mytable WHERE column1 LIKE 'prod*' AND column2 LIKE 'food'
    However, if I run it in my VB6 code like following, those records are not deleted.
    Code:
    cn.Open strConnectionstring
    cn.Execute "DELETE FROM mytable WHERE column1 LIKE 'prod*' AND column2 LIKE 'food'"
    cn.Close
    I don't know why this happen.
    I'm wondering if anybody can give me some advice.

    Thanks in advance.
    Last edited by jdy0803; Feb 13th, 2018 at 05:01 PM.

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,734

    Re: DELETE with LIKE condition

    Try % instead of *

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Sep 2012
    Posts
    387

    Re: DELETE with LIKE condition

    Works fine!!!
    I realized MS Access use * as a wildcard but ADO use % as a wildcard.

    Thanks!

  4. #4
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    12,377

    Re: DELETE with LIKE condition

    Also since you are not using a wildcard on the second field you should not be using like there, instead you should use =
    Code:
    cn.Execute "DELETE FROM mytable WHERE column1 LIKE 'prod%' AND column2 = 'food'"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width