?
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
Medium Priority
?
264 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

718 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