MS Access Query: Code to Cut String

Posted on 2009-04-24
Medium Priority
Last Modified: 2012-05-06
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.

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.

Question by:openaccount1
  • 3
  • 2
LVL 25

Expert Comment

ID: 24222722
Hi openaccount1,

something like (in MS Access)... ?

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


Author Comment

ID: 24222757
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.
LVL 25

Accepted Solution

lwadwell earned 2000 total points
ID: 24222772

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")


Author Comment

ID: 24222782
Wow! Right on the spot! Quick and excellent! Thank you so much!

Author Closing Comment

ID: 31574128
Solution was quick and right on the spot. Did not encounter any error nor clarify anything. Thanks alot lwadwell.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

839 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