Split Function Creates Error 13 Type Mismatch

[Steve]

I am trying to fill the array MySearchArray with the results of the split of
my text field, yet it returns an error 13. I have used this code in two other
databases and it has worked fine but now I am getting errors. I have marked
the line below that creates the error.

Thanks

Private Sub cmdSearch_Click()
Dim MySQL, MySearchText As String
Dim MySearchArray() As Variant
Dim I As Integer
MySearchArray = Split(Me.txtSearch, " ") 'ERROR OCCURS HERE!
If Not IsEmpty(MySearchArray) Then
If UBound(MySearchArray) = 0 Then
MySearchText = "*" & MySearchArray(0) & "*"
Else
For I = 0 To UBound(MySearchArray)
MySearchText = MySearchText & "*" & MySearchArray(I) & "*"
Next I
End If
End If
MySQL = "SELECT HotKey from tblC3Notes "
MySQL = MySQL + "WHERE C3NoteTxt "
MySQL = MySQL + "LIKE '" & MySearchText & "' ORDER BY HotKey"
DoCmd.OpenForm "frmFindC3Note", acNormal
Forms![frmFindC3Note]!List1.RowSourceType = "Table/Query"
Forms![frmFindC3Note]!List1.RowSource = MySQL
Forms![frmFindC3Note]!List1.Requery
If Forms![frmFindC3Note]!List1.ListCount = 0 Then
MsgBox "Your search returned no results. Please try again.", _
vbOKOnly + vbInformation, "Sorry..."
DoCmd.Close acForm, "frmFindC3Note", acSaveNo
End If
Me.txtSearch = ""
Me.txtSearch.SetFocus
End Sub

 

[John Spencer]

Try changing you Dim statement.
Dim MySearchArray as Variant

n
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Douglas J. Steele]

Change

Dim MySearchArray() As Variant

to

Dim MySearchArray As Variant

or to

Dim MySearchArray() As String


Incidentally, your line of code

Dim MySQL, MySearchText As String

may not be doing what you think it is.

That only declares MySearchText as a string: MySQL is declared as a variant.
You can't "short circuit" declarations. To have both as strings, you need
to use

Dim MySQL As String, MySearchText As String

 

[Douglas J. Steele]

No, MySearchArray does not have to be declared as String.

Sub x()
Dim a As Variant

a = Split("A B C", " ")

End Sub

works fine.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi -

A quick test of this in A2003 shows that MySearchArray must be declared as
a
String. I got error 13 when I used Variant in this simple sub:

Sub x()
Dim a() As Variant
a = Split("A B C", " ")
End Sub

and changing it to String for a() worked fine.

Give that a try

John

I am trying to fill the array MySearchArray with the results of the split
of
my text field, yet it returns an error 13. I have used this code in two
other
databases and it has worked fine but now I am getting errors. I have
marked
the line below that creates the error.

Thanks

Private Sub cmdSearch_Click()
Dim MySQL, MySearchText As String
Dim MySearchArray() As Variant
Dim I As Integer
MySearchArray = Split(Me.txtSearch, " ") 'ERROR OCCURS HERE!
If Not IsEmpty(MySearchArray) Then
If UBound(MySearchArray) = 0 Then
MySearchText = "*" & MySearchArray(0) & "*"
Else
For I = 0 To UBound(MySearchArray)
MySearchText = MySearchText & "*" & MySearchArray(I) & "*"
Next I
End If
End If
MySQL = "SELECT HotKey from tblC3Notes "
MySQL = MySQL + "WHERE C3NoteTxt "
MySQL = MySQL + "LIKE '" & MySearchText & "' ORDER BY HotKey"
DoCmd.OpenForm "frmFindC3Note", acNormal
Forms![frmFindC3Note]!List1.RowSourceType = "Table/Query"
Forms![frmFindC3Note]!List1.RowSource = MySQL
Forms![frmFindC3Note]!List1.Requery
If Forms![frmFindC3Note]!List1.ListCount = 0 Then
MsgBox "Your search returned no results. Please try again.", _
vbOKOnly + vbInformation, "Sorry..."
DoCmd.Close acForm, "frmFindC3Note", acSaveNo
End If
Me.txtSearch = ""
Me.txtSearch.SetFocus
End Sub

 

[Dirk Goldgar]

Interesting - note that I used dim a(), explicitly indicating an array;
when
I declared "a()" as Variant it failed; but when I declared A() as String,
it
worked.

The difference is subtle (or so it seems) - can you give some detail on
why I
would have to explicitly declare A() as String in this instance? Just
curious.

The Split function will return an array of String elements. Consider these
results from the Immediate Window:

?TypeName(Split("A B"))
String()

I'm not sure whether the Split function returns String() -- that is, an
array of String elements -- directly, or a Variant containing String().
Either way, the result can successfully be assigned to a variable defined as
String(), or to a variable defined as Variant (because a Variant can hold
anything). It can't be assigned to a a variable defined as Variant() --
that is, as an array of Variant elements -- because the elements of the
array are not variants, they are strings.

 

[David W. Fenton]

Try changing you Dim statement.
Dim MySearchArray as Variant

Don't you need the parens for it to work, i.e.:

Dim MySearchArray() as Variant

Or can you split into a variant without needing that?

 

[Dirk Goldgar]

Don't you need the parens for it to work, i.e.:

Dim MySearchArray() as Variant

No, that would be declaring an array of variants (which can't be assigned
the array of strings that Split returns), rather than a variant that may
contain an array.

 

[Jack Leach]

Dim MySearchArray() As Variant

Remove the parentheses from the MySearchArray declaration:

Dim MySearchArray As Variant


hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Jack Leach]

hmm.. I replied to this once but it didn't seem to show up...

Dim MySearchArray() As Variant

Remove the parentheses from this variable declaration...


--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

I am trying to fill the array MySearchArray with the results of the split of
my text field, yet it returns an error 13. I have used this code in two other
databases and it has worked fine but now I am getting errors. I have marked
the line below that creates the error.

Thanks

Private Sub cmdSearch_Click()
Dim MySQL, MySearchText As String
Dim MySearchArray() As Variant
Dim I As Integer
MySearchArray = Split(Me.txtSearch, " ") 'ERROR OCCURS HERE!
If Not IsEmpty(MySearchArray) Then
If UBound(MySearchArray) = 0 Then
MySearchText = "*" & MySearchArray(0) & "*"
Else
For I = 0 To UBound(MySearchArray)
MySearchText = MySearchText & "*" & MySearchArray(I) & "*"
Next I
End If
End If
MySQL = "SELECT HotKey from tblC3Notes "
MySQL = MySQL + "WHERE C3NoteTxt "
MySQL = MySQL + "LIKE '" & MySearchText & "' ORDER BY HotKey"
DoCmd.OpenForm "frmFindC3Note", acNormal
Forms![frmFindC3Note]!List1.RowSourceType = "Table/Query"
Forms![frmFindC3Note]!List1.RowSource = MySQL
Forms![frmFindC3Note]!List1.Requery
If Forms![frmFindC3Note]!List1.ListCount = 0 Then
MsgBox "Your search returned no results. Please try again.", _
vbOKOnly + vbInformation, "Sorry..."
DoCmd.Close acForm, "frmFindC3Note", acSaveNo
End If
Me.txtSearch = ""
Me.txtSearch.SetFocus
End Sub

 

[David W. Fenton]

No, that would be declaring an array of variants (which can't be
assigned the array of strings that Split returns), rather than a
variant that may contain an array.

I've never used Split() with anything but a string array, since I've
never Split() anything but strings. Now that I've given it some
thought, other than an array of arrays, I can't see why you'd ever
declare an array as type Variant, whether or not you think you're
populating it with Split().

I've never done it but I assume that if you declared your array as a
numeric type, VBA would coerce the string values that come from
Split() into the appropriate numeric type (I guess I could test it,
but it would be kind of inconvenient right this second).

 

[Dirk Goldgar]

I've never used Split() with anything but a string array, since I've
never Split() anything but strings. Now that I've given it some
thought, other than an array of arrays, I can't see why you'd ever
declare an array as type Variant, whether or not you think you're
populating it with Split().

I can imagine circumstances where you might do it, but other data structures
make more sense.

I've never done it but I assume that if you declared your array as a
numeric type, VBA would coerce the string values that come from
Split() into the appropriate numeric type (I guess I could test it,
but it would be kind of inconvenient right this second).

No, that doesn't work, either. I took the time to test it. Consider the
following code:

'------ start of code ------
Sub TestSplitAssignment()

Dim SimpleVariant As Variant
Dim VariantArray() As Variant
Dim StringArray() As String
Dim SimpleString As String
Dim LongArray() As Long

Const StringToSplit As String = "1 2 3 5"

On Error Resume Next

Err.Clear
StringArray = Split(StringToSplit)
Debug.Print "Assign to String Array ==> Err ="; _
Err.Number, Err.Description

Err.Clear
SimpleVariant = Split(StringToSplit)
Debug.Print "Assign to Simple Variant ==> Err ="; _
Err.Number, Err.Description

Err.Clear
VariantArray = Split(StringToSplit)
Debug.Print "Assign to Variant Array ==> Err ="; _
Err.Number, Err.Description

Err.Clear
SimpleString = Split(StringToSplit)
Debug.Print "Assign to Simple String ==> Err ="; _
Err.Number, Err.Description

Err.Clear
LongArray = Split(StringToSplit)
Debug.Print "Assign to Long Array ==> Err ="; _
Err.Number, Err.Description

End Sub
'------ end of code ------

When executed, it displays this in the Immediate Window:

Assign to String Array ==> Err = 0
Assign to Simple Variant ==> Err = 0
Assign to Variant Array ==> Err = 13 Type mismatch
Assign to Simple String ==> Err = 13 Type mismatch
Assign to Long Array ==> Err = 13 Type mismatch

So VBA is not willing to coerce the string array returned by Split() into an
array of another type. It *is* willing to set a simple Variant to point to
that array.

 

[Steve]

I took out the parentheses and it works. Thanks for the info about variable
declaration, too. I saw that in other pieces of code on the net I thought it
would apply the data type to all the variables.

This is a lot of good information on the split function. Thanks a lot.

P.S. I checked the box to get notified of replies, but I never a got a
notice of a reply. Is this function no longer working? Since I never got a
message saying there was a reply, I thought no one was responding to it until
I signed in to see if by chance I got a reply but didn't get a message
stating such.

Steve

Try changing you Dim statement.
Dim MySearchArray as Variant

n
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I am trying to fill the array MySearchArray with the results of the split of
my text field, yet it returns an error 13. I have used this code in two other
databases and it has worked fine but now I am getting errors. I have marked
the line below that creates the error.

Thanks

Private Sub cmdSearch_Click()
Dim MySQL, MySearchText As String
Dim MySearchArray() As Variant
Dim I As Integer
MySearchArray = Split(Me.txtSearch, " ") 'ERROR OCCURS HERE!
If Not IsEmpty(MySearchArray) Then
If UBound(MySearchArray) = 0 Then
MySearchText = "*" & MySearchArray(0) & "*"
Else
For I = 0 To UBound(MySearchArray)
MySearchText = MySearchText & "*" & MySearchArray(I) & "*"
Next I
End If
End If
MySQL = "SELECT HotKey from tblC3Notes "
MySQL = MySQL + "WHERE C3NoteTxt "
MySQL = MySQL + "LIKE '" & MySearchText & "' ORDER BY HotKey"
DoCmd.OpenForm "frmFindC3Note", acNormal
Forms![frmFindC3Note]!List1.RowSourceType = "Table/Query"
Forms![frmFindC3Note]!List1.RowSource = MySQL
Forms![frmFindC3Note]!List1.Requery
If Forms![frmFindC3Note]!List1.ListCount = 0 Then
MsgBox "Your search returned no results. Please try again.", _
vbOKOnly + vbInformation, "Sorry..."
DoCmd.Close acForm, "frmFindC3Note", acSaveNo
End If
Me.txtSearch = ""
Me.txtSearch.SetFocus
End Sub

.

 

[Douglas J. Steele]

I believe you'll only get a response if the responder used the web
interface.

Most of the regulars here refuse to use the web interface to these
newsgroups, preferring to use a news reader.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I took out the parentheses and it works. Thanks for the info about variable
declaration, too. I saw that in other pieces of code on the net I thought
it
would apply the data type to all the variables.

This is a lot of good information on the split function. Thanks a lot.

P.S. I checked the box to get notified of replies, but I never a got a
notice of a reply. Is this function no longer working? Since I never got a
message saying there was a reply, I thought no one was responding to it
until
I signed in to see if by chance I got a reply but didn't get a message
stating such.

Steve

Try changing you Dim statement.
Dim MySearchArray as Variant

n
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I am trying to fill the array MySearchArray with the results of the
split of
my text field, yet it returns an error 13. I have used this code in two
other
databases and it has worked fine but now I am getting errors. I have
marked
the line below that creates the error.

Thanks

Private Sub cmdSearch_Click()
Dim MySQL, MySearchText As String
Dim MySearchArray() As Variant
Dim I As Integer
MySearchArray = Split(Me.txtSearch, " ") 'ERROR OCCURS HERE!
If Not IsEmpty(MySearchArray) Then
If UBound(MySearchArray) = 0 Then
MySearchText = "*" & MySearchArray(0) & "*"
Else
For I = 0 To UBound(MySearchArray)
MySearchText = MySearchText & "*" & MySearchArray(I) & "*"
Next I
End If
End If
MySQL = "SELECT HotKey from tblC3Notes "
MySQL = MySQL + "WHERE C3NoteTxt "
MySQL = MySQL + "LIKE '" & MySearchText & "' ORDER BY HotKey"
DoCmd.OpenForm "frmFindC3Note", acNormal
Forms![frmFindC3Note]!List1.RowSourceType = "Table/Query"
Forms![frmFindC3Note]!List1.RowSource = MySQL
Forms![frmFindC3Note]!List1.Requery
If Forms![frmFindC3Note]!List1.ListCount = 0 Then
MsgBox "Your search returned no results. Please try again.", _
vbOKOnly + vbInformation, "Sorry..."
DoCmd.Close acForm, "frmFindC3Note", acSaveNo
End If
Me.txtSearch = ""
Me.txtSearch.SetFocus
End Sub

.

 

[Jack Leach]

I still use the web interface, so he should have gotten a response there. On
the other hand, the interface was slow that day and my responses (or anyone
elses) weren't reflected through the web interface until about 8hrs later.

Probably just a bug on the interface end... suprise suprise

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

I believe you'll only get a response if the responder used the web
interface.

Most of the regulars here refuse to use the web interface to these
newsgroups, preferring to use a news reader.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I took out the parentheses and it works. Thanks for the info about variable
declaration, too. I saw that in other pieces of code on the net I thought
it
would apply the data type to all the variables.

This is a lot of good information on the split function. Thanks a lot.

P.S. I checked the box to get notified of replies, but I never a got a
notice of a reply. Is this function no longer working? Since I never got a
message saying there was a reply, I thought no one was responding to it
until
I signed in to see if by chance I got a reply but didn't get a message
stating such.

Steve

Try changing you Dim statement.
Dim MySearchArray as Variant

n
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Steve wrote:
I am trying to fill the array MySearchArray with the results of the
split of
my text field, yet it returns an error 13. I have used this code in two
other
databases and it has worked fine but now I am getting errors. I have
marked
the line below that creates the error.

Thanks

Private Sub cmdSearch_Click()
Dim MySQL, MySearchText As String
Dim MySearchArray() As Variant
Dim I As Integer
MySearchArray = Split(Me.txtSearch, " ") 'ERROR OCCURS HERE!
If Not IsEmpty(MySearchArray) Then
If UBound(MySearchArray) = 0 Then
MySearchText = "*" & MySearchArray(0) & "*"
Else
For I = 0 To UBound(MySearchArray)
MySearchText = MySearchText & "*" & MySearchArray(I) & "*"
Next I
End If
End If
MySQL = "SELECT HotKey from tblC3Notes "
MySQL = MySQL + "WHERE C3NoteTxt "
MySQL = MySQL + "LIKE '" & MySearchText & "' ORDER BY HotKey"
DoCmd.OpenForm "frmFindC3Note", acNormal
Forms![frmFindC3Note]!List1.RowSourceType = "Table/Query"
Forms![frmFindC3Note]!List1.RowSource = MySQL
Forms![frmFindC3Note]!List1.Requery
If Forms![frmFindC3Note]!List1.ListCount = 0 Then
MsgBox "Your search returned no results. Please try again.", _
vbOKOnly + vbInformation, "Sorry..."
DoCmd.Close acForm, "frmFindC3Note", acSaveNo
End If
Me.txtSearch = ""
Me.txtSearch.SetFocus
End Sub
.

.

 

[Jack Leach]

It can't be assigned to a a variable defined as Variant() --

that is, as an array of Variant elements -- because the elements of the
array are not variants, they are strings.

Odd... one would think that a Variant element of an array could hold a
string... that's what the Variant datatype is used for after all. Strange
that a Variant can hold string elements, but a Variant element can't hold a
string.

This is one of those things like trying to figure out how a Boolean is
typcasted, or what actually goes on when you compile a module... better left
taken for granted than get into a hairpullingout situation trying to
understand

In any case, I knew we couldn't use Variant() as an array for the Split
function, but the testing done on the matter sheds at least some light on the
reasons why.

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Dirk Goldgar]

Odd... one would think that a Variant element of an array could hold a
string... that's what the Variant datatype is used for after all. Strange
that a Variant can hold string elements, but a Variant element can't hold
a
string.

That's not what I said. What I said was that an array of Variant is a
different thing than an array of String, and VBA won't automatically convert
one to the other. You could easily write code to allocate a variant array
of the same dimensions and assign each element of the string array to the
corresponding element of the variant array; however, VBA won't do that on
its own.

 

[vanderghast]

A Variant can hold a string and an array of string. The problem is...like
once Jim Ferguson said, is that "who tells you VB is logical?" (and he was
an active defender of VB)

The first part is standard poutine, but the second part is a case of as
"logical as VB can be" (not to denigrate VB, in general, though)


Dim v() As Variant
ReDim v(0 To 1) As Variant
v(0) = "1 2 3 4" ' String
v(1) = Split(v(0), " ") ' array of strings


Dim q As Variant
Dim w() As Variant
Dim z() As String


q = Array("one", "two", "three")
w = q
' z = q ' line c2

q = Split(v(0), " ")
' w = q ' line c1
z = q




About the second part.
As it is, w( ) as Variant accept an array of string returned by Array, but
not by Split. (Uncomment line c1 to experience the second part of the claim)
Worse. z( ) as String does NOT accept an array of string returned by Array,
but accepts one returned by Split. (Uncomment line c2 to experience the
first part of the claim)


So, if an array of strings cannot be assigned to an array of strings...
well, not always at least... That is not making VBA 'bad', but just
'particular' in some cases, since at least these are trapped by the
compiler, anyhow... Standard poutine.



Vanderghast, Access MVP

 

[vanderghast]

And note that VB defines the result of Split as being an array, while the
result of Array as being a variant (holding an array). So while the
difference is subtle, in most cases, the compiler see enough difference to
complain between the two.


Vanderghast, Access MVP

 

[Dirk Goldgar]

A Variant can hold a string and an array of string. The problem is...like
once Jim Ferguson said, is that "who tells you VB is logical?" (and he was
an active defender of VB)

The first part is standard poutine, but the second part is a case of as
"logical as VB can be" (not to denigrate VB, in general, though)


Dim v() As Variant
ReDim v(0 To 1) As Variant
v(0) = "1 2 3 4" ' String
v(1) = Split(v(0), " ") ' array of strings


Dim q As Variant
Dim w() As Variant
Dim z() As String


q = Array("one", "two", "three")
w = q
' z = q ' line c2

q = Split(v(0), " ")
' w = q ' line c1
z = q




About the second part.
As it is, w( ) as Variant accept an array of string returned by Array, but
not by Split.

I believe that the Array() function returns a Variant containing an array of
Variant, not of String, even if all the arguments to the function are
strings. That explains the behavior you have documented.

 

[Dirk Goldgar]

I believe that the Array() function returns a Variant containing an array
of Variant, not of String, even if all the arguments to the function are
strings. That explains the behavior you have documented.

To provide some technical underpinning for this, consider:

a = Array("a", "b", "c") : ?VarType(a)
8204

a = Split("a b c"): ?Vartype(a)
8200

Notes on the VarType values:

8204 = 8192 (Array) + 12 (Variant)
8200 = 8192 (Array) + 8 (String)