Solved

Query to grab part of a text field

Posted on 2002-04-12
12
357 Views
Last Modified: 2008-03-06
Hello,

I created a text file that shows me files on my computer. The path is included with the filename. For example, it says: c:\my documents\whatever\myfile.doc. I imported this text file into Access.

In my Access table, I have a column for the filename. I would like to create a query that will return to me only the filename. I would like to keep the filename and path seperate. I would like to have two queries, actually. I would like one to return to me the filename and the other to return the path.

I probably need to create some sort of query that starts at the right of the field and goes to the left until it reaches a backslash '\'. Then, give me the text.

Can you think of a simple query to do this and/or using the query wizard?

Thanks in advance,

John
0
Comment
Question by:jhieb
  • 3
  • 3
  • 3
  • +2
12 Comments
 
LVL 12

Accepted Solution

by:
Paurths earned 50 total points
ID: 6937702
hi

paste this into a new module:

Public Function GiveFile(strPath As String) As String
Dim i As Integer

    For i = Len(strPath) To 1 Step -1
        If Mid(strPath, i, 1) = "\" Then
            'FileFound
            GiveFile = Right(strPath, Len(strPath) - i)
            Exit For
        End If
       
    Next i

End Function

Public Function GivePath(strPath As String) As String
Dim i As Integer

    GivePath = Left(strPath, Len(strPath) - Len(GiveFile(strPath)))

End Function



now,in your query u can use these functions like this
e.g. name of fieldname that holds the path is 'fldPath'

paste them into the top row of a new column:

ThePath: givepath([fldPath])

TheFile: givefile([fldPath])


query looks like this
SELECT tblPaths.fldPath, givepath([fldPath]) AS ThePath, givefile([fldPath]) AS TheFile
FROM tblPaths;


cheers
Ricky
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6937716
Your "pseudo-code" to extract the filename and path is correct, however you need to write a Function to extract the path and/or the filename OR to locate the last "/" position, enabling you to use string commands to extract the wanted part in your query.

You would get something like:

SELECT left(FileName,fncLastSlash(FileName)-1) AS Path, MID(FileName,fncLastSlash(FileName)) AS FileNameClean FROM....
 
What do you want to code yourself ?

Nic;o)
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6937720
Spoiling the fun of coding Ricky ?
BTW what about the function to return the last "/", saves coding ;-)

Nic;o)
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 1

Author Comment

by:jhieb
ID: 6937773

I will take your word on it that Ricky's code will work. If I could program like that I don't think I would have this problem. Sorry, I am pretty green at functions and coding so it looks like greek to me. I am also trying to figure out how I would implement Nic;o's function. If I were to use the build wizard, in the query, would I be able to piece together what you have written or is there really no easier way?

Thanks,

John
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6937796
Hi John,

Could be a good reason to start coding in Access, as it will enlarge your possibilities.

Ricky has created two functions you need to copy/paste into a new module (see last database tab).
After that the application will know what to do and you're able to use the function in your query as Ricky describes.
Just place the FileName field in the query editor (doubleclick the field in the table visible) and change the fieldname into:
ThePath: givepath([fldPath])

Now the query should show only the Path in a column named "ThePath"
(Same goes for the file)

I just "optimized" this to one function that will only return the location of the last "/".
Now you can use the standard LEFT and MID function to extract the path (LEFT) and the file (MID).

What you want to use is just a matter of preference, it will work both.

As I always enjoy coding for the satisfaction when it works as intended/designed, I always start with asking what a questionner wants.

An easier way (without functions) isn't possible as far as I know, sorry.

Nic;o)
0
 
LVL 3

Expert Comment

by:Bob Scriver
ID: 6938171
Here is a Function that can be useful in other situations beyond this situation:

Public Function InStrRev(vStartPoint As Integer, vPath As String, vTargetStr As String) As String
Dim i As Integer

   For i = vStartPoint To 1 Step -1
       If InStr(i, vPath, vTargetStr, 1) = i Then
           InStrRev = i   'Position of vTarget
           Exit For
       End If
       
   Next i

End Function

It is an InStr function starting at the right.  Can be used for many situations.  Have always written this function to search from the right and it works quite well.

To use it in your situation paste the following into the sql property of a query, while changing the table and field names to match yours.

SELECT tblPaths.Path, Mid$([tblPaths]![Path],InStrRev(Len([tblPaths]![Path]),[tblPaths]![Path],"\")+1) AS FileName
FROM Customers;

This query will display the full path in one column and the filename in the next.

The parameters are variable so the function can be used to search from the right of any string for any target string that you would like.  Just pass the appropriate variables to the Function.

Bob Scriver
0
 
LVL 3

Expert Comment

by:Bob Scriver
ID: 6938618
Update to SQL.

Use this SQL to guard against a null value in the path field.  Also, provides for the column PathOnly(including last "\":

SELECT tblPaths.Path, IIf(Not IsNull([tblPaths]![Path]),Mid$([tblPaths]![Path],1,InStrRev(Len([tblPaths]![Path]),[tblPaths]![Path],"\"))," ") AS PathOnly, IIf(Not IsNull([tblPaths]![Path]),Mid$([tblPaths]![Path],InStrRev(Len([tblPaths]![Path]),[tblPaths]![Path],"\")+1), " "
AS FileName
FROM tblPaths;

Bob Scriver
0
 
LVL 3

Expert Comment

by:Bob Scriver
ID: 6938619
Sorry, missing closing ")"

SELECT tblPaths.Path, IIf(Not IsNull([tblPaths]![Path]),Mid$([tblPaths]![Path],1,InStrRev(Len([tblPaths]![Path]),[tblPaths]![Path],"\")),"
") AS PathOnly, IIf(Not IsNull([tblPaths]![Path]),Mid$([tblPaths]![Path],InStrRev(Len([tblPaths]![Path]),[tblPaths]![Path],"\")+1),
" ")
AS FileName
FROM tblPaths;

Bob Scriver
0
 
LVL 1

Author Comment

by:jhieb
ID: 6939070
Thank you for answering my question. You were the first person to respond so I am giving you the points. All of you have been helpful and it is dificult not giving all of you points.

Thank you for your help.

John
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6939818
hi jhieb,

glad i could help,

just wondering, why the 'B'-grade?

Ricky
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6943295
i guess i'll just keep wondering...
0
 

Expert Comment

by:framos1
ID: 8706685
scriverb
just wanted to say thanks.
This old article saved my butt!

thx.

-Frank
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
hit enter key to run macro 13 23
Data Type Mismatch in Criteria - Access 6 28
Macro to import XML in Access 2013 2 33
Update a text value in another table 10 35
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
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, 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 …

816 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

7 Experts available now in Live!

Get 1:1 Help Now