Solved

Query to grab part of a text field

Posted on 2002-04-12
12
355 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

757 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

19 Experts available now in Live!

Get 1:1 Help Now