We help IT Professionals succeed at work.

parse out value from field in access query

Mark Drelinger
on
I need help parsing out a value from a string in Access Query.

My field has
         DS_AS_AT_20150826_10010.pdf

I need to parse out the customer number between "_" and ".pdf"..
The number can be bigger or smaller, but will always precede with the underscore and end with the period.
Is this possible within the access query ?
Comment
Watch Question

Dale FyeOwner, Dev-Soln LLC
SILVER EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:

will it always look like this?


AA_AA_AA_YYYYMMDD_CUST#.pdf?


if so you can create a function fnParseCust that looks like:

Public Function fnParseCust(SomeValue as variant) as Variant

    dim strArr() as string

    fnParseCust = NULL
    if isnull(SomeValue) then exit function

    strArr = Split(SomeValue, "_")
    if ubound(strArr) < 4 then
        'value did not meet the defined structure
   else
        fnParseCust = Replace(strArr(4), ".pdf", "")
    endif

End function

Then you just need to call that function from your query:

SELECT *, fnParseCust([SomeField]) as CustFromFilename FROM yourTable

Test your restores, not your backups...
SILVER EXPERT
Expert of the Year 2019
Top Expert 2016
Commented:

Another approach would be to extract it with some string function right in the quesry, with a calculated column.  Try something like:


cust: Mid([Text1],InStrRev([Text1],"_")+1,InStrRev([Text1],".")-InStrRev([Text1],"_")-1)

Author

Commented:
Both worked... I used Bills.  thanks to both.
Bill PrewTest your restores, not your backups...
SILVER EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:

Welcome, glad that helped.

Dale FyeOwner, Dev-Soln LLC
SILVER EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:

only problem with Bills will be if the field you are attempting to parse is NULL then the Instr( ) function will raise an error.  So, unless you are absolutely certain that that field will always contain a value, you might try concatenating an empty string to every reference to that field.


cust: Mid([Text1],InStrRev("" & [Text1],"_")+1,InStrRev("" & [Text1],".")-InStrRev("" & [Text1],"_")-1)


Author

Commented:
Thanks Dale - in this case, the table is pulling in the data from file names in a directory - so there wouldn't be nulls. But i could see the in string function erroring out in many cases.

As always, i appreciate your expertise and insight !

Regards,
Mark Drelinger

Explore More ContentExplore courses, solutions, and other research materials related to this topic.