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
257 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 119

Accepted Solution

by:
Rey Obrero 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

911 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

16 Experts available now in Live!

Get 1:1 Help Now