?
Solved

Query to grab part of a text field

Posted on 2002-04-12
12
Medium Priority
?
362 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
[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
  • 3
  • 3
  • +2
12 Comments
 
LVL 12

Accepted Solution

by:
Paurths earned 150 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

752 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