Splitting in Access

Greeting,

I have a txt field in Access 2013 database with the following sample data.

2012 Ford F150 Truck

and I would like to split it into the following output.

str1: 2012
str2: Ford
str3: F150 Truck

Pls provide sample code.

Thanks in advance.
mrongAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
That would be similar to splitting out city state zip.

See my sample code here:

http://hitechcoach.com/index.php?option=com_content&view=article&id=25:parsing-out-city-state-and-zip&catid=27:vba
0
Rey Obrero (Capricorn1)Commented:
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 = "Null"

  Else

      fnParseInfo = myArray(idx - 1)

   End If

End Function

Open in new window



then create a query like this

select  fnParseInfo([textFieldName], 1) as str1,fnParseInfo([textFieldName], 2) as str2, fnParseInfo([textFieldName], 3) & " " & fnParseInfo([textFieldName], 4) as str3
from tableX


change "textFieldName" with actual name of the field
.
0
mrongAuthor Commented:
capricorn1,

Tried your suggestion and got Run-time error '94' at line-> myArray = Split(vString, Delimiter)
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Rey Obrero (Capricorn1)Commented:
post the query you are using..

you may need to add a where clause to your query

where [fieldName] is not null
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
I took the code in the link I provided and modified for you.

Place the following in a standard code module

Option Compare Database
Option Explicit

' ******** Code Start ********
 'This code was originally written by Boyd Trimmell aka HiTechCoach.

Public Function FlipIt(InData As String) As String

 Dim p As Integer
 Dim strOut As String

 strOut = ""
 For p = Len(InData) To 1 Step -1
 
 strOut = strOut & Mid(InData, p, 1)
 
 Next p

 FlipIt = strOut


End Function


Public Function GetModel(InData As String) As String

Dim strOut As String
Dim strWRK As String
Dim p As Long
Dim p2 As Long
Dim p3 As Long

strWRK = (Trim(InData))
strOut = ""

p = InStr(strWRK, " ")
If p > 0 Then
 ' found a year end


 strWRK = Trim(Mid(strWRK, p))
 p2 = InStr(strWRK, " ")

 If p2 > 0 Then
 ' found model
 
 strWRK = Trim(Mid(strWRK, p2))
 
 ' found city
 strOut = strWRK
 
 ' remove a , from end of city
 If Left(strOut, 1) = "," Then
 strOut = Trim(Mid(strOut, 2))
 End If
 
 strOut = strOut
 
 
 End If


End If

GetModel = strOut

End Function

Public Function GetMake(InData As String) As String

Dim strOut As String
Dim strWRK As String
Dim p As Long
Dim p2 As Long
Dim p3 As Long

strWRK = Trim(InData)
strOut = ""

p = InStr(strWRK, " ")
If p > 0 Then
 ' found a year

 strWRK = Trim(Mid(strWRK, p))

 p2 = InStr(strWRK, " ")
 ' found state end
 
 strOut = Trim(Left(strWRK, p2))


End If

GetMake = strOut

End Function

Public Function GetYear(InData As String) As String

Dim strOut As String
Dim strWRK As String
Dim p As Long
Dim p2 As Long
Dim p3 As Long

strWRK = (Trim(InData))
strOut = ""

p = InStr(strWRK, " ")
If p > 0 Then
 ' found a zip end
 p2 = InStr(p, strWRK, " ")

 strOut = Trim(Left(strWRK, p))


End If

GetYear = strOut

End Function
' ********** Code End **********

Open in new window


Example:

? GetYear( "2012 Ford F150 Truck")
2012

? GetMake( "2012 Ford F150 Truck")
Ford

? GetModel( "2012 Ford F150 Truck")
F150 Truck

Open in new window



Example in a query use:

GetYear([MyFieldNameHere]) as ModelYear

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Be sure that your data conforms to the rules you're stating. For example data like this:

2012 Ford F 150 Truck

Or

2012 Ford F150 CrewCab Truck

May not parse correctly.
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
LSMConsulting makes a good point.

The code I posted expect the first word to be the year. The second word to be the make. Everything else is the model.

The code I posted will handle

2012 Ford F 150 Truck
2012 Ford F150 CrewCab Truck

If the Make is two words then my code will not work.

This will work

2014 Mercedes-Benz SL-Class SL550

but not

2014 Mercedes Benz SL-Class SL550
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.