MS Access Query: Code to Cut String

Posted on 2009-04-24
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.

Filename: myfirstpage.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
    LVL 25

    Expert Comment

    Hi openaccount1,

    something like (in MS Access)... ?

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


    Author Comment

    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


    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

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

    Author Closing Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    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…

    761 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

    12 Experts available now in Live!

    Get 1:1 Help Now