Solved

find rows using instr in vba recordset

Posted on 2014-09-22
3
280 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 85
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 50

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

724 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