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
262 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 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

615 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