• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 672
  • Last Modified:

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

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.
  • 4
  • 2
  • 2
1 Solution
JavaRossAuthor Commented:
Ah, I forgot an example, so here it is:

Sample Data [varchar(250) SampleColumn]:

Sample desired value of SELECT

You say they are unsupported by Access ADO but what about using the functions in a UDF from ADO?
Leigh PurvisDatabase DeveloperCommented:
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).
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

JavaRossAuthor Commented:
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.
JavaRossAuthor Commented:
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.
Post your UDF and I may be able to help debug it.
Leigh PurvisDatabase DeveloperCommented:
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?
JavaRossAuthor Commented:
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.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now