Solved

find rows using instr in vba recordset

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access Web appliction 7 46
Contents of subform based upon combobox value 3 23
Running sum query 6 25
Sub Reports 8 21
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

932 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

8 Experts available now in Live!

Get 1:1 Help Now