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
256 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
Comment Utility
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
Comment Utility
Perfecto, thank you so much Rey Obrero!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

771 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

11 Experts available now in Live!

Get 1:1 Help Now