• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

find rows using instr in vba recordset

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
PeterBaileyUk
Asked:
PeterBaileyUk
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You could do it with SQL:

SELECT * FROM YourTable WHERE YourField LIKE '*luxury business/media/cherry*'
0
 
PeterBaileyUkAuthor Commented:
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
 
Gustav BrockCIOCommented:
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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now