Solved

MS Access Query Parse String Into X Num of Column Based on X Num of Backslash in STring

Posted on 2015-01-08
2
260 Views
Last Modified: 2015-01-08
Hi, I'm trying to parse a string in an Access query into it's own column.  The field called "filename" has exactly 6 backlashes which I would like each element to be in 7 different columns.  I'm open to creating a function so I can easily change the number of instances a backslash may occur.  Thanks in advance for the help!

Sample Table
Filename
D:\Imaging\WebDocs\Finance\Vendor\2008\vsax_1021101723_001.pdf
D:\Imaging\WebDocs\Finance\Vendor\2009\intern_0814142131_001.pdf
D:\Imaging\WebDocs\Finance\Financial Reporting\2006\murr_1028122641_001.pdf
ect...

Query Results
Column1  Column2    Column3  Column4  Column5                     Column6   Column7
D:               Imagining  WebDocs  Finance    Vendor                         2008          vsax_1021101723_001.pdf
D:               Imagining  WebDocs  Finance    Vendor                         2009          intern_0814142131_001.pdf
D:               Imagining  WebDocs  Finance    Financial Reporting    2006          vsax_1021101723_001.pdf
0
Comment
Question by:KANEDA 0149
2 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 40538315
place this codes in a regular module

Function fnParseInfo(vString As Variant, idx As Integer, Optional Delimiter As String = "\") As String

   Dim myArray() As String
   myArray = Split(vString, Delimiter)

   If idx < 0 Or idx > UBound(myArray) + 1 Then

      fnParseInfo = ""

  Else

      fnParseInfo = myArray(idx - 1)

   End If

End Function

Open in new window


then create a query


select  [Filename], fnParseInfo( [Filename],1) as column1
, fnParseInfo( [Filename],2) as column2
, fnParseInfo( [Filename],3) as column3
, fnParseInfo( [Filename],4) as column4
, fnParseInfo( [Filename],5) as column5
, fnParseInfo( [Filename],6) as column6
, fnParseInfo( [Filename],7) as column7
From yourtable
0
 

Author Closing Comment

by:KANEDA 0149
ID: 40538347
Perfecto, thank you so much Rey Obrero!
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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.​
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

685 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