Split() function tips/tricks?

  • Thread starter Thread starter RD
  • Start date Start date
R

RD

I saw reference to this in a different thread and was interested. I looked in
Help and on the MSDN site and can't seem to get it to work. This looks like it
could be a very usfull function but I keep getting "out of range" or "type
mismatch" errors. Does anybody have a piece of working code incorporating this
function that I could take a look at? Maybe with some tips or insights on
usage?

TIA,
RD
 
Take a look at
http://support.microsoft.com/default.aspx?scid=kb;en-us;266289.

Although this article is for VB 6, the code works just fine in VBA. The only
difference is that VB names it's first button Command1 and Access names it's
Command0. Change either the button name or the sub procedure to match the
other.

Good luck.

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Denver Area Access Users Group Vice President www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
Useful Metric Conversion #18 of 19: 8 nickels = 2 paradigms (My personal
favorite)
Miscellaneous Access and VB "stuff" at www.ScoBiz.com
 
I saw reference to this in a different thread and was interested. I looked in
Help and on the MSDN site and can't seem to get it to work. This looks like it
could be a very usfull function but I keep getting "out of range" or "type
mismatch" errors. Does anybody have a piece of working code incorporating this
function that I could take a look at? Maybe with some tips or insights on
usage?

TIA,
RD
Click to expand...


Here is an example that uses a hard coded comma to spit a string on.

Public Function ParseText(TextIn As String, X) As Variant
On Error Resume Next
Dim VAR As Variant
VAR = Split(TextIn, ",", -1)
ParseText = VAR(X)

End Function

You can call it from a query, for instance, using:

FirstName:ParseText([FullName], 1)

Which will return
John
(the 2nd split value) if the full name was "Smith, John".

The split function is Zero based, so you need to refer to the first
split value result as (0), the second as (1), etc.

You can alter the arguments in the above function to allow more user
flexibility in selecting the character to split on, i.e. a space
instead of the comma used in the above sample.
 
The split function is not 0 based. any array addressing is dependant on the
database option you are using. (Option Base). For Access, the default is 0,
but at the module level you can declare Option Base 1 if you want the lower
bound to be 1. 0 and 1 are the only choices.

Also, Be careful trying to parse names with such a simple approach. There
are too many variations in names for any algorithmn to be 100% accurate.

fredg said:
I saw reference to this in a different thread and was interested. I looked in
Help and on the MSDN site and can't seem to get it to work. This looks like it
could be a very usfull function but I keep getting "out of range" or "type
mismatch" errors. Does anybody have a piece of working code incorporating this
function that I could take a look at? Maybe with some tips or insights on
usage?

TIA,
RD
Click to expand...


Here is an example that uses a hard coded comma to spit a string on.

Public Function ParseText(TextIn As String, X) As Variant
On Error Resume Next
Dim VAR As Variant
VAR = Split(TextIn, ",", -1)
ParseText = VAR(X)

End Function

You can call it from a query, for instance, using:

FirstName:ParseText([FullName], 1)

Which will return
John
(the 2nd split value) if the full name was "Smith, John".

The split function is Zero based, so you need to refer to the first
split value result as (0), the second as (1), etc.

You can alter the arguments in the above function to allow more user
flexibility in selecting the character to split on, i.e. a space
instead of the comma used in the above sample.
Click to expand...
 
Take a look at
http://support.microsoft.com/default.aspx?scid=kb;en-us;266289.

Although this article is for VB 6, the code works just fine in VBA. The only
difference is that VB names it's first button Command1 and Access names it's
Command0. Change either the button name or the sub procedure to match the
other.
Click to expand...

Hm! I'm getting the "Subscript out of range" error just like with the MSDN
example. I don't think my computer likes the Split() function.
 
I saw reference to this in a different thread and was interested. I looked in
Help and on the MSDN site and can't seem to get it to work. This looks like it
could be a very usfull function but I keep getting "out of range" or "type
mismatch" errors. Does anybody have a piece of working code incorporating this
function that I could take a look at? Maybe with some tips or insights on
usage?

TIA,
RD
Click to expand...


Here is an example that uses a hard coded comma to spit a string on.

Public Function ParseText(TextIn As String, X) As Variant
On Error Resume Next
Dim VAR As Variant
VAR = Split(TextIn, ",", -1)
ParseText = VAR(X)

End Function

You can call it from a query, for instance, using:

FirstName:ParseText([FullName], 1)

Which will return
John
(the 2nd split value) if the full name was "Smith, John".

The split function is Zero based, so you need to refer to the first
split value result as (0), the second as (1), etc.

You can alter the arguments in the above function to allow more user
flexibility in selecting the character to split on, i.e. a space
instead of the comma used in the above sample.
Click to expand...

This is giving me the "Sub or function not defined." compile error. I don't
know what's going on but I just can't get Split() to work on my machine.
 
Post your code, please. The example you referenced is fine. Also, check to
see if you have Option Base defined in your module. If it is not defined,
then the default is 0.

For example, in the code example, 4 elements are created. Fred, Wilma,
Barney, and Betty. So, the first element of strArray = "Fred ". If you are
using Option Base 0, then it would be strArray(0) and strArry(3) = "Betty ".
If you are using Option Base 1, then it would be strArray(1) = "Fred" and
strArray(4) = "Betty".
 
The array it returns apparently is 0 based. From Help:
"Description
Returns a zero-based, one-dimensional array containing a specified number of
substrings."

And, you're quite right, I wouldn't use this for names but I often find myself
parsing out comma delimited strings and using the InStr() function gets tedious.


The split function is not 0 based. any array addressing is dependant on the
database option you are using. (Option Base). For Access, the default is 0,
but at the module level you can declare Option Base 1 if you want the lower
bound to be 1. 0 and 1 are the only choices.

Also, Be careful trying to parse names with such a simple approach. There
are too many variations in names for any algorithmn to be 100% accurate.

fredg said:
I saw reference to this in a different thread and was interested. I looked in
Help and on the MSDN site and can't seem to get it to work. This looks like it
could be a very usfull function but I keep getting "out of range" or "type
mismatch" errors. Does anybody have a piece of working code incorporating this
function that I could take a look at? Maybe with some tips or insights on
usage?

TIA,
RD
Click to expand...


Here is an example that uses a hard coded comma to spit a string on.

Public Function ParseText(TextIn As String, X) As Variant
On Error Resume Next
Dim VAR As Variant
VAR = Split(TextIn, ",", -1)
ParseText = VAR(X)

End Function

You can call it from a query, for instance, using:

FirstName:ParseText([FullName], 1)

Which will return
John
(the 2nd split value) if the full name was "Smith, John".

The split function is Zero based, so you need to refer to the first
split value result as (0), the second as (1), etc.

You can alter the arguments in the above function to allow more user
flexibility in selecting the character to split on, i.e. a space
instead of the comma used in the above sample.
Click to expand...
Click to expand...
 
I saw reference to this in a different thread and was interested. I looked in
Help and on the MSDN site and can't seem to get it to work. This looks like it
could be a very usfull function but I keep getting "out of range" or "type
mismatch" errors. Does anybody have a piece of working code incorporating this
function that I could take a look at? Maybe with some tips or insights on
usage?

TIA,
RD
Click to expand...

Ok ... I just got it to work. I'm having other issues with Access, now. Under
different header.

Thanks go to Sco, Fred and Klatuu,
RD
 
That's the reason I like the code in the KB article.

Because it uses LBound and UBound instead of hard coding values, it works
properly with both Option Base 0 and Option Base 1.

If this isn't working, something else is wrong. My first question which I
never asked would be; Does your code compile? You might be having a
reference problem.

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Denver Area Access Users Group Vice President www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
Useful Metric Conversion #18 of 19: 8 nickels = 2 paradigms (My personal
favorite)
Miscellaneous Access and VB "stuff" at www.ScoBiz.com


Klatuu said:
Post your code, please. The example you referenced is fine. Also, check to
see if you have Option Base defined in your module. If it is not defined,
then the default is 0.

For example, in the code example, 4 elements are created. Fred, Wilma,
Barney, and Betty. So, the first element of strArray = "Fred ". If you are
using Option Base 0, then it would be strArray(0) and strArry(3) = "Betty ".
If you are using Option Base 1, then it would be strArray(1) = "Fred" and
strArray(4) = "Betty".


Click to expand...
 
What is highlighted after the compile error?

This sounds like you might have a bad reference.

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Denver Area Access Users Group Vice President www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
Useful Metric Conversion #18 of 19: 8 nickels = 2 paradigms (My personal
favorite)
Miscellaneous Access and VB "stuff" at www.ScoBiz.com


RD said:
I saw reference to this in a different thread and was interested. I looked in
Help and on the MSDN site and can't seem to get it to work. This looks like it
could be a very usfull function but I keep getting "out of range" or "type
mismatch" errors. Does anybody have a piece of working code incorporating this
function that I could take a look at? Maybe with some tips or insights on
usage?

TIA,
RD
Click to expand...


Here is an example that uses a hard coded comma to spit a string on.

Public Function ParseText(TextIn As String, X) As Variant
On Error Resume Next
Dim VAR As Variant
VAR = Split(TextIn, ",", -1)
ParseText = VAR(X)

End Function

You can call it from a query, for instance, using:

FirstName:ParseText([FullName], 1)

Which will return
John
(the 2nd split value) if the full name was "Smith, John".

The split function is Zero based, so you need to refer to the first
split value result as (0), the second as (1), etc.

You can alter the arguments in the above function to allow more user
flexibility in selecting the character to split on, i.e. a space
instead of the comma used in the above sample.
Click to expand...

This is giving me the "Sub or function not defined." compile error. I don't
know what's going on but I just can't get Split() to work on my machine.
Click to expand...
Click to expand...
 
Please share what was wrong that you fixed.

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Denver Area Access Users Group Vice President www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
Useful Metric Conversion #18 of 19: 8 nickels = 2 paradigms (My personal
favorite)
Miscellaneous Access and VB "stuff" at www.ScoBiz.com
 
RD,

I couldn't find your "...other issues with Access, now. Under different
header..."

Did you post with a different name and/or in a different group?

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Denver Area Access Users Group Vice President www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
Useful Metric Conversion #18 of 19: 8 nickels = 2 paradigms (My personal
favorite)
Miscellaneous Access and VB "stuff" at www.ScoBiz.com
 
RD,

I couldn't find your "...other issues with Access, now. Under different
header..."

Did you post with a different name and/or in a different group?

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Denver Area Access Users Group Vice President www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
Useful Metric Conversion #18 of 19: 8 nickels = 2 paradigms (My personal
favorite)
Miscellaneous Access and VB "stuff" at www.ScoBiz.com
Click to expand...

It's like the old Western serials I used to go see when I was a kid.
The bad guys are closing in on the hero and his girl. The house they
are in is on fire. He's been shot. Just then, as the timbers start to
fall, the message is shown on screen "Continued next week!".
And then the movie company goes out of business.
It's enough to keep one awake all night ... wondering... wondering....
:-)
 
It's like the old Western serials I used to go see when I was a kid.
The bad guys are closing in on the hero and his girl. The house they
are in is on fire. He's been shot. Just then, as the timbers start to
fall, the message is shown on screen "Continued next week!".
And then the movie company goes out of business.
It's enough to keep one awake all night ... wondering... wondering....
:-)
Click to expand...

LOL!

I never did get around to posting under another header. Just as I was about to
send that post I had an epiphany.

So, for one thing, I hadn't turned off the dreaded Name AutoCorrect "feature".
For another I was just calling the darn thing wrong. Once I called it correctly
it worked flawlessly.

Name AutoCorrect wasn't allowing me to F8 into my code. It just kept beeping at
me. I turned off AutoCorrect, decompiled and recompiled and I'm a happy camp
.... er, coder once again.

Anyway, thanks for all your help and suggestions.

RD
 
Back
Top