Query to grab part of a text field
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.
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
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.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Spoiling the fun of coding Ricky ?
BTW what about the function to return the last "/", saves coding ;-)
Nic;o)
BTW what about the function to return the last "/", saves coding ;-)
Nic;o)
ASKER
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
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)
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)
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],InS
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
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],InS
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
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]),
AS FileName
FROM tblPaths;
Bob Scriver
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]),
AS FileName
FROM tblPaths;
Bob Scriver
Sorry, missing closing ")"
SELECT tblPaths.Path, IIf(Not IsNull([tblPaths]![Path]),
") AS PathOnly, IIf(Not IsNull([tblPaths]![Path]),
" ")
AS FileName
FROM tblPaths;
Bob Scriver
SELECT tblPaths.Path, IIf(Not IsNull([tblPaths]![Path]),
") AS PathOnly, IIf(Not IsNull([tblPaths]![Path]),
" ")
AS FileName
FROM tblPaths;
Bob Scriver
ASKER
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
Thank you for your help.
John
hi jhieb,
glad i could help,
just wondering, why the 'B'-grade?
Ricky
glad i could help,
just wondering, why the 'B'-grade?
Ricky
i guess i'll just keep wondering...
scriverb
just wanted to say thanks.
This old article saved my butt!
thx.
-Frank
just wanted to say thanks.
This old article saved my butt!
thx.
-Frank
You would get something like:
SELECT left(FileName,fncLastSlash
What do you want to code yourself ?
Nic;o)