Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Extract Data from Access Record

Posted on 2015-01-24
2
Medium Priority
?
161 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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

688 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