ADO and Access: Obtaining substring from end of string based on delimiter

Posted on 2006-05-01
Last Modified: 2008-01-09
Using an ADO SQL query against MS Access, how do you obtain a varchar substring from the end of a varchar field based on a delimiter?

I have searched for an answer in a number of ways, trying to find alternates for the InStrRev, Reverse and StrReverse methods (all of which are unsupported by Access via ADO), but I have not been able to find a previously documented direct answer to this question.

Please note again, for an acceptable answer, this must be performed in a SELECT statement via ADO against Microsoft Access. I can not use VBA or VB code for this solution (if that was all I needed, I would not be posting this).

More Info:
  - Visual Basic 6.0 SP6
  - MS ADO 2.7
  - MS Access 2000 (and newer)

Thanks for any experienced insight on this delima.
Question by:JavaRoss
    LVL 1

    Author Comment

    Ah, I forgot an example, so here it is:

    Sample Data [varchar(250) SampleColumn]:

    Sample desired value of SELECT

    LVL 44

    Expert Comment

    You say they are unsupported by Access ADO but what about using the functions in a UDF from ADO?
    LVL 44

    Expert Comment

    by:Leigh Purvis
    Have you tried having a saved query in Access - using a pretty standard method like

    SELECT Mid([YourField],InStrRev([YourField],'.')+1) FROM tblTable WHERE InStr([YourField],'.')>0

    And using your ADO connection to just open that query?
    See if Access can help you as much as possible. :-)

    (And if need be - then use your own UDFs in the query - Ray's advice).
    LVL 1

    Author Comment

    Thank you GRayL and LPurvis,

    I have considered the possible option of user defined functions, however it is an undesirable solution because the particular SELECT statement that I am attempting to create is part of an upgrade process that will pull information from an existing previously installed/deployed customer database for use in the creation of a new upgraded table/database. I am attempting to form the SELECT statement in such a way that the resulting recordset may be passed to the same Load method in my VB code that is used to load the data from databases using the new database schema, rather than creating seperate special-cased load methods for each upgrade scenario, polluting my object model and making a maintenance headache.

    I will look into that option further, however, while waiting to see if anyone has additional comments, suggestions or insights.
    LVL 1

    Author Comment

    Since this seems to be stumping people, I'll raise the point value to 250.

    In looking into the user defined function option, it does not appear to be working out to well. I have tried to create a user defined function and I tried creating a procedure, both of which failed. The procedure failed for the same reason that the SELECT fails - it stated the InStrRev function was undefined. The user defined procedure failed because it was unable to create the defined return table, so it may just be a syntax error on my part.

    I have progressed along a different pass to side-step this issue, but I would still like to find a solution in case I need it now or later.
    LVL 44

    Expert Comment

    Post your UDF and I may be able to help debug it.
    LVL 44

    Accepted Solution

    I don't think UDF's will help - as ADO still won't be able to evaluate them.
    They're just not available.

    You'll probably hate the workaround (as some people think DAO has had it - which it clearly hasn't).
    Use DAO. :-)
    Open an instance of Access - and obtain a database object from that.
    Then evaluate the recordset fields - they'll have access to the functions.

    Something like

        Dim db As Database
        Dim rst As dao.Recordset
        Dim objAccess As Object
        Set objAccess = CreateObject("Access.Application")
        objAccess.OpenCurrentDatabase "H:\db2.mdb"
        Set db = objAccess.Currentdb
        Set rst = db.OpenRecordset("SELECT Mid([YourField],InStrRev([YourField],'.')+1) FROM tblTable WHERE InStr([YourField],'.')>0")
        Debug.Print rst(0)
        Set rst = Nothing
        Set db = Nothing
        Set objAccess = Nothing

    Is it pretty? No.
    Quick?  Not particularly (though DAO is generally quicker with Jet data than ADO is - but the Access instance will slow it down).

    Apart from this - you'll be opening up a ADO recordset with no functions - and doing the parsing yourself in code on the raw recordset values returned - which you've said you can't use.
    (And you specified ADO as a requirement).

    Your answer looks like "No - you can't"

    What is the workaround you've implemented?
    LVL 1

    Author Comment

    Thanks LPurvis,
    Admittedly, I'm one of the ones that avoids DAO. My current solution is pure ADO and I am loathe to add additional dependencies, etc. In working through this, I re-evaluated a bit and ended up enhancing my primary load method to more robustly accept values and interpret them as needed, giving me more leeway in what values I pull in.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    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

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    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…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    779 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

    15 Experts available now in Live!

    Get 1:1 Help Now