MS Access Query: Code to Cut String

Hi We have a database which we would like to customize. First off here are the columns.

1. URL --> URL of a page
2. Filename --> filename of page that is in the URL.

What we would like is to have a code that can get the data from the URL field then cut the text to only show the filename.

e.g.
URL: http://www.mysite.com/en/mypages/myfirstpage.html
Filename: myfirstpage.html

URL: http://www.mysite.com/mymainpage.html
Filename: mymainpage.html

so that the only thing that needs to be inserted are the URLs and it will automatically create the filename. I would prefer the code to be in the Query so that we can create a "Make Table" query out of it.


Thanks.
openaccount1Asked:
Who is Participating?
 
lwadwellConnect With a Mentor Commented:
lwadwell,

You would then need a replace (this assumes .html will only occur once in the file name) ...

Replace(Mid( URL, InstrRev ( URL, "/" ) +1 ) , ".html", ".doc")

lwadwell
0
 
lwadwellCommented:
Hi openaccount1,

something like (in MS Access)... ?

select Mid( URL, InstrRev ( URL, "/" ) +1 ) as filename
from yourtable


lwadwell
0
 
openaccount1Author Commented:
OK, tried and it works! Thanks... one more thing. how about renaming the filename to .doc instead of .html...
so the code will remove the path as well as change the .html to .doc.
0
 
openaccount1Author Commented:
Wow! Right on the spot! Quick and excellent! Thank you so much!
0
 
openaccount1Author Commented:
Solution was quick and right on the spot. Did not encounter any error nor clarify anything. Thanks alot lwadwell.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.