Solved

find rows using instr in vba recordset

Posted on 2014-09-22
3
258 Views
Last Modified: 2014-10-08
I have outputs of vehicles of a client that detail the trim level of the car.

ex

luxury business/med
luxury business/media
luxury business/media/cherry


in this case luxury business/med is in luxury business/media as luxury business/media is in luxury business/media/cherry

i want to be able to find luxury business/media/cherry and remove the other two.

not sure how to do that or if its even possible

of course i would know the current row words in the string so maybe i could build up a new string as the recordset is already sorted.

i would be doing this in vba in access
0
Comment
Question by:PeterBaileyUk
3 Comments
 
LVL 84
ID: 40336928
You could do it with SQL:

SELECT * FROM YourTable WHERE YourField LIKE '*luxury business/media/cherry*'
0
 

Author Comment

by:PeterBaileyUk
ID: 40336974
so if i take the first row of the recordset then pass that to this type of query then take the last longest row?

I have a record set of x rows and need to eliminate the rows where they are instrs of others.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40337315
You can do it like this:

DELETE
    *
FROM
    YourTable
WHERE
    YourTable.YourField <> 
        (Select Top 1 YourField From YourTable As T Where T.YourField Like YourTable.YourField & "*");

/gustav
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

770 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