Solved

Extract Data from Access Record

Posted on 2015-01-24
2
155 Views
Last Modified: 2015-01-24
I need to extract data between _xxx_ based on the sample records below  to a separate Access field... for example MP1
Also I need to extract the data between the last underscore and the period to a separate Access field... for example Mike Smith

Sample Records:
 05_History_MP1_Patricia Ashley.csv
06_Math_MP2_Mike Smith.csv
0
Comment
Question by:shieldsco
[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
2 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 40568184
are all the files .csv files?

place this function in a regular module

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

   Dim myArray() As String
   vString = Replace(vString, ".csv", "")
   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 like this

select [fieldname], fnParseInfo([fieldname], 3) , fnParseInfo([fieldname], 4)
from tableName

.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40568193
to handle al types of files
change

vString = Replace(vString, ".csv", "")
with

vString= Left(vString, InStr(vString, ".") - 1)
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

739 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