Splitting Text with Delimiter

[ebarrera]

I have this table (imported from a text file) with a field name called COURSE. The value of course is a text field, with a format of ACCT-101-501. I need to seperate the value into three sections where the delimiter is "-". I currently use Excel to seperate (text-to-column) and then copy it back into my table but want to see if I can do this in Access with a query. I have read that Split() would this for me, but I hace Access 2002 and don't see my company upgrading soon.

The result I want is three fields (Discipline, CourseNum, Section). The value types should be (Text, Text, Text). Is there a function in Access 2000 that can do this, if not what is the workaround?

 

[Kiwiman]

Howzit

In the import wizard in access, have you tried selecting the Delimiter as "Other" and specifying the "-" in the text box next to it?

 

[RuralGuy]

...I have read that Split() would this for me, but I hace Access 2002 and don't see my company upgrading soon.

The result I want is three fields (Discipline, CourseNum, Section). The value types should be (Text, Text, Text). Is there a function in Access 2000 that can do this, if not what is the workaround?

Here's a link that should help.

 

[ebarrera]

Kiwiman, the text file is comma delimitted and works for me to get the 1st seperation. I need one more seperation but directed at only one field.

RuralGuy, I will try the link and get back to you

Thanks.

 

[ebarrera]

I am just not seeing it, maybe it is because I am not a programmer and use the design view to create the query.

I am thinking iif() will work, by nesting the left(), mid() and right() and checking for "*-*". So far, this worked for my Discpline seperation. I am now working on the CourseNum and Section. I bet the Split() function would have made this easier for me.

 

[boblarson]

I bet the Split() function would have made this easier for me.

Actually, the split function has been available at least since Access 2000. But, you have to create a function to use it (as far as I know).

Here's a function for you:

Public Function SplitFile(intField As Integer, strValue As String, strDelimiter As String) As String
Dim varSplit As Variant

varSplit = Split(strValue, strDelimiter, , vbTextCompare)

SplitFile = varSplit(intField)

End Function

All you have to do then is create your columns in the query:

MyField1:SplitFile(1,[YourUnSplitFieldName],",")

MyField2:SplitFile(2,[YourUnSplitFieldName],",")

MyField3:SplitFile(3,[YourUnSplitFieldName],",")


Also, I didn't put in error handling in case you supplied the wrong number for the argument, but you can do that if you wish.

 

[ebarrera]

boblarson - Thanks, I have never created a function, but googled it and created one in the module and called it SplitFile. Afterwards i entered the following:

Discipline: SplitFile(1,[ASSIGNMENT],"-")

I get an error that says "undefined function 'SplitFile' in expression. I used "-" because it is the delimiter. What am I doing wrong? Thanks again in advance.

 

[boblarson]

Did you put the function into a module and name the module something OTHER THAN SplitFile. You can't name the module the same as the function.

 

[ebarrera]

Aha! Something to remember from now on. I changed it and it now work like a charm. I had just finished my iif() for Section when you gave this to me. It worked but definitely a lot more work. This way is better. Do you know if this forum has a posting of useful functions for Access 2000 users, like this?

 

[boblarson]

There are things like this in the code repository, but we have so much but you have to search for it or browse for it. We don't really have a catalogued repository.

 

[jacc14]

Actually, the split function has been available at least since Access 2000. But, you have to create a function to use it (as far as I know).

Here's a function for you:

Public Function SplitFile(intField As Integer, strValue As String, strDelimiter As String) As String
Dim varSplit As Variant
 
varSplit = Split(strValue, strDelimiter, , vbTextCompare)
 
SplitFile = varSplit(intField)
 
End Function

All you have to do then is create your columns in the query:

MyField1:SplitFile(1,[YourUnSplitFieldName],",")

MyField2:SplitFile(2,[YourUnSplitFieldName],",")

MyField3:SplitFile(3,[YourUnSplitFieldName],",")


Also, I didn't put in error handling in case you supplied the wrong number for the argument, but you can do that if you wish.

Hi. I have searched the web for hours to find the code which does the job for me and finally found your code which works a treat. However you mention that the code may need error handling. I need this as get SUBSCRIPT OF RANGE because i have supplied the wrong number of arguments on some of the fields which return #error. please could you advice how i do this as i am quite a newby to VB.

Many thanks in anticipation

 

[raskew]

Hi -
This series of steps, plugged into a query, will return each section regardless of the size of the sections:

a = "ACCT-101-501"
b = left(a, instr(a, "-")-1)
c = Mid(a,len(b)+2, len(a)- instrrev(a, "-"))
d = mid(a, instrrev(a, "-")+1)

[I]? a
[B]ACCT-101-501[/B]
? b
[B]ACCT[/B]
? c
[B]101[/B]
? d
[B]501[/B][/I]

HTH - Bob

 

[jacc14]

Hi -
This series of steps, plugged into a query, will return each section regardless of the size of the sections:

a = "ACCT-101-501"
b = left(a, instr(a, "-")-1)
c = Mid(a,len(b)+2, len(a)- instrrev(a, "-"))
d = mid(a, instrrev(a, "-")+1)
 
[I]? a[/I]
[I][B]ACCT-101-501[/B][/I]
[I]? b[/I]
[I][B]ACCT[/B][/I]
[I]? c[/I]
[I][B]101[/B][/I]
[I]? d[/I]
[I][B]501[/B][/I]

HTH - Bob

Hi Bob, Thanks for that , it does the job.
Kind regards
Chris

 

[kikeask]

Actually, the split function has been available at least since Access 2000. But, you have to create a function to use it (as far as I know).

Here's a function for you:

Public Function SplitFile(intField As Integer, strValue As String, strDelimiter As String) As String
Dim varSplit As Variant

varSplit = Split(strValue, strDelimiter, , vbTextCompare)

SplitFile = varSplit(intField)

End Function

All you have to do then is create your columns in the query:

MyField1:SplitFile(1,[YourUnSplitFieldName],",")

MyField2:SplitFile(2,[YourUnSplitFieldName],",")

MyField3:SplitFile(3,[YourUnSplitFieldName],",")


Also, I didn't put in error handling in case you supplied the wrong number for the argument, but you can do that if you wish.

First Thanks this is the solution to my problem, I only need help what to do because sometime my fields are no the same every time, it contain 2 or 3 or 4 or 6 but never have the same and when it fail I have an error that say

Run-time error '9'
Subscript out of range

can you please help me with this error?

Thanks in advanced for your help.

 

[Mirica_Victor]

First Thanks this is the solution to my problem, I only need help what to do because sometime my fields are no the same every time, it contain 2 or 3 or 4 or 6 but never have the same and when it fail I have an error that say

Run-time error '9'
Subscript out of range

can you please help me with this error?

Thanks in advanced for your help.

Today I had the same problem and starting from boblarson's code I came to this :

Public Function SplitFile(intField As Integer, strValue As String, strDelimiter As String) As String
Dim varSplit As Variant
If (InStr(1, strValue, strDelimiter) <= 1 Or Null) And (intField = 1) Then
    SplitFile = strValue
Else
    Dim NumDelim As Integer
    NumDelim = 0
    Dim PosDelim As Integer
    PosDelim = 1
 
    Do
        If InStr(PosDelim, strValue, strDelimiter) Then
        NumDelim = NumDelim + 1
        PosDelim = InStr(PosDelim, strValue, strDelimiter)
        End If
    Loop Until InStr(PosDelim + 1, strValue, strDelimiter) <= 1
    If intField > NumDelim + 1 Then
        SplitFile = 0
    Else
        varSplit = Split(strValue, strDelimiter, , vbTextCompare)
        SplitFile = varSplit(intField - 1)
    End If
End If
End Function

If you input a value that ecceeds the number of splited fields it will trow "0".

Little hint:

From boblarson's code the line

SplitFile = varSplit(intField)

since the first field in an array in indexed with 0, should be:

SplitFile = varSplit(intField-1)

Hope works for you as it did for me!

 

[CalSchroeck]

Actually, the split function has been available at least since Access 2000. But, you have to create a function to use it (as far as I know).

Here's a function for you:

Public Function SplitFile(intField As Integer, strValue As String, strDelimiter As String) As String
Dim varSplit As Variant
 
varSplit = Split(strValue, strDelimiter, , vbTextCompare)
 
SplitFile = varSplit(intField)
 
End Function

All you have to do then is create your columns in the query:

MyField1:SplitFile(1,[YourUnSplitFieldName],",")

MyField2:SplitFile(2,[YourUnSplitFieldName],",")

MyField3:SplitFile(3,[YourUnSplitFieldName],",")


Also, I didn't put in error handling in case you supplied the wrong number for the argument, but you can do that if you wish.

Below is a variant of boblarsons original where I incorporated the improvement of adding the "-1" suggested by another and further added the error handling. My final result shown below worked when I put it in a module in Microsoft (R) Access 2003 database and the query formats of boblarson shown in the quote above. Below is the module.

Public Function SplitFile(intField As Integer, strValue As String, strDelimiter As String) As String
Dim varSplit As Variant
On Error GoTo Err_SplitFile
varSplit = Split(strValue, strDelimiter, , vbTextCompare)
SplitFile = varSplit(intField - 1)

Err_SplitFile:
End Function

 

[David Ball]

Hi,

The text I am trying to split is of the type:

CN-MSTGN-GN-GN-010, CN-MSTGN-GN-GN-000, CN-MSTGN-GN-GN-010, CN-MSTGN-GN-GN-000




I have tried the code:

Public Function SplitFile(intField As Integer, strValue As String, strDelimiter As String) As String
Dim varSplit As Variant
If (InStr(1, strValue, strDelimiter) <= 1 Or Null) And (intField = 1) Then
SplitFile = strValue
Else
Dim NumDelim As Integer
NumDelim = 0
Dim PosDelim As Integer
PosDelim = 1

Do
If InStr(PosDelim, strValue, strDelimiter) Then
NumDelim = NumDelim + 1
PosDelim = InStr(PosDelim, strValue, strDelimiter)
End If
Loop Until InStr(PosDelim + 1, strValue, strDelimiter) <= 1
If intField > NumDelim + 1 Then
SplitFile = 0
Else
varSplit = Split(strValue, strDelimiter, , vbTextCompare)
SplitFile = varSplit(intField - 1)
End If
End If
End Function


I get the error:

Run-time error '6' Overfow

What do I need to change?

Thanks very much

Dave

 

[spikepl]

Begin by saying what you are trying to accomplish. Split into what exactly? Hard to guess from first glance at the code.

 

[jdraw]

David,

That is a 2 year old post.
Can you show us how you called this function?
Post your code so readers can see your efforts.

Good luck.