Solved

find rows using instr in vba recordset

Posted on 2014-09-22
3
265 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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

839 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