Solved

Query to grab part of a text field

Posted on 2002-04-12
12
358 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

828 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