• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 465
  • Last Modified:

Type mismatch when using array from SQL statement

The previous programmer put all the employee's tasks in one field, separated by commas.  So, the field looks like: 224, 335, 453, 256.  What I need to do is now take this field, split it out into an array and then for each value update the Selected column in another table.  However, when it gets to the strSplit() - Split(rst!ConstantTas, ", ") line, it gives me a type mismatch error.  This is in an ACCESS 2003 database written in VBA.

Sandra


Public Sub TestArray()
Dim strSelect As String
Dim strUpdate As String
Dim rst As DAO.Recordset
Dim strSplit() As Variant
Dim i As Integer

gstrUserID = "sgsmith"
strSelect = "SELECT UserID, ConstantTask, VariableTask " & _
    "FROM TblUsers WHERE TblUsers.UserID= '" & gstrUserID & "' "
   
Set rst = CurrentDb.OpenRecordset(strSelect)
rst.MoveFirst
With rst
    If Len(rst!ConstantTask) > 0 Then
    strSplit() = Split(rst!ConstantTask, ", ")   ERRORS OUT HERE
        For i = LBound(strSplit) To UBound(strSplit)
        strUpdate = "UPDATE tblTEMPConAssign SET tblTEMPConAssign.Selected = Yes " & _
            "WHERE tblTEMPConAssign.ConstantTCodeID = " & Split(i)
        Next i
    End If

    If Len(rst!VariableTask) > 0 Then
    strSplit = Split(rst!VariableTask, ", ")
        For i = LBound(strSplit) To UBound(strSplit)
        strUpdate = "UPDATE tblTEMPVarAssign SET tblTEMPVarnAssign.Selected = Yes " & _
            "WHERE tblTEMPVarAssign.VariableTCodeID = " & Split(i)
        Next i
    End If
    .Close
End With
Set rst = Nothing
End Sub
0
Sandra Smith
Asked:
Sandra Smith
  • 9
  • 5
  • 3
1 Solution
 
mbizupCommented:
Try dropping the parentheses:

>>> strSplit() = Split(rst!ConstantTask, ", ")   ERRORS OUT HERE


Try this instead:

strSplit = Split(rst!ConstantTask, ", ")
0
 
mbizupCommented:
Oh - also dim your array as string:


Dim strSplit() As String
0
 
Sandra SmithRetiredAuthor Commented:
New version below, but now nothing is returned in the array.

Public Sub TestArray()
Dim strSelect As String
Dim strUpdate As String
Dim rst As DAO.Recordset
Dim strSplit() As String
Dim i As Integer

gstrUserID = "sgsmith"
strSelect = "SELECT UserID, ConstantTask, VariableTask " & _
    "FROM TblUsers WHERE TblUsers.UserID= '" & gstrUserID & "' "
   
Set rst = CurrentDb.OpenRecordset(strSelect)
rst.MoveFirst
With rst
    If Len(rst!ConstantTask) > 0 Then
    strSplit = Split(rst!ConstantTask, ", ")
        For i = LBound(strSplit) To UBound(strSplit)
        strUpdate = "UPDATE tblTEMPConAssign SET tblTEMPConAssign.Selected = Yes " & _
            "WHERE tblTEMPConAssign.ConstantTCodeID = " & i
        Next i
    End If

    If Len(rst!VariableTask) > 0 Then
    strSplit = Split(rst!VariableTask, ", ")
        For i = LBound(strSplit) To UBound(strSplit)
        strUpdate = "UPDATE tblTEMPVarAssign SET tblTEMPVarnAssign.Selected = Yes " & _
            "WHERE tblTEMPVarAssign.VariableTCodeID = " & i
        Next i
    End If
    .Close
End With
Set rst = Nothing
End Sub
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Sandra SmithRetiredAuthor Commented:
Ok, it may be the split,  The contents of the field are all numbers, no quotes, so perhaps teh split statement should be changed?  What is actually being return is

"224, 225, 237, 346,"

So there is also the problem of leading and trailing quotes with the last comma an issue.  But this probably is another question on how to trim an array - or trim the string first.

Sandra
0
 
mbizupCommented:
If your data includes quotes, change your split to the following:


strSplit = Split(Replace(rst!ConstantTask, chr(39),"") , ", ")
0
 
Sandra SmithRetiredAuthor Commented:
Got rid of the quotes, but is simply does not recognize the array.  Simply returns 0 rather than each item in the array.

Sandra
0
 
Sandra SmithRetiredAuthor Commented:
Maybe I am approaching this incorrectly.  if the field is in teh format itself of:
   234, 236, 647, 357,
with just the final comma, do I need to Split?  Is this not already an array?  Could I not define the array as
    strSplit = rst!ConstantTask

Sandra

NOTE:  Nope that did not work.
0
 
mbizupCommented:
Your split statement is fine.

I think your update statement is wrong.

Are you trying to update according to the ID corresponding to each array element?

For the first:


  If Len(rst!ConstantTask) > 0 Then
    strSplit = Split(rst!ConstantTask, ", ")
        For i = 0 To UBound(strSplit)
        strUpdate = "UPDATE tblTEMPConAssign SET tblTEMPConAssign.Selected = Yes " & _
            "WHERE tblTEMPConAssign.ConstantTCodeID = " & strsplit(i)
        Next i
    End If
0
 
Rey Obrero (Capricorn1)Commented:
you need to have

currentdb.execute strUpdate, dbfailonerror


to make this work

and another thing
if the field ConstantTCodeID is Number data type, change the where clause to

WHERE tblTEMPConAssign.ConstantTCodeID = " & cint(strsplit(i))


if text

WHERE tblTEMPConAssign.ConstantTCodeID = '" & cstr(strsplit(i)) & "'"
0
 
Sandra SmithRetiredAuthor Commented:
mbizup, yes.  I have in my temp table a column with all the task codes and another column called Select.  What I am trying to do is to get the valeus from the TblUsers table that has all their tasks in one field lumped together, I need to split that out so I can get each code, then update the temp table Selected to Yes where the user has a corresponding task code.  this is why I need to loop thorugh the array from the TblUsers table so I can update the tblTEMPConAssign and tblTEMPVarAssign tables.

Sandra
0
 
Sandra SmithRetiredAuthor Commented:
mbizup, I tried your version and it gives me a subscript out ofrange serror.  It simply won't recognize any of the elements or even if it is an array.  Cap, will try your suggestion next.

Sandra
0
 
Sandra SmithRetiredAuthor Commented:
Error MessageOk, Cap got something I comletely missed. There was do command to actually run the code, but I still get a run-time error.  I have attached the error message.

Sandra
0
 
Rey Obrero (Capricorn1)Commented:
you should also consider that there must be inconsistencies in the content of field "ConstantTask", so to avoid that, do this

dim strTask as string
strTask=replace(rst!ConstantTask, " ","")     ' remove the spaces
 
 strSplit = Split(strTask, ",")
0
 
Sandra SmithRetiredAuthor Commented:
So, rather than returning an element, it is returning the entire string

If Len(rst!ConstantTask) > 0 Then
   
    strSplit = Split(rst!ConstantTask, ", ")
        For i = 0 To UBound(strSplit)
        strUpdate = "UPDATE tblTEMPConAssign SET tblTEMPConAssign.Selected = Yes " & _
            "WHERE tblTEMPConAssign.ConstantTCodeID = " & strSplit(i)
        CurrentDb.Execute strUpdate, dbFailOnError
        Next i
    End If
0
 
Rey Obrero (Capricorn1)Commented:
sandra,


just what i was thinking, see my post at http:#a37779029
0
 
Sandra SmithRetiredAuthor Commented:
Your suggestions worked.  Below is the final working code.  Mbizup, thank you for trying to help.  I was close to simply giving up.Sandra

Public Sub TestArray()
Dim strSelect As String
Dim strUpdate As String
Dim rst As DAO.Recordset
Dim strSplit() As String
Dim strTasks As String
Dim i As Integer
Dim strTask As String

strSelect = "SELECT UserID, ConstantTask, VariableTask " & _
    "FROM TblUsers WHERE TblUsers.UserID= '" & gstrUserID & "' "
   
Set rst = CurrentDb.OpenRecordset(strSelect)
rst.MoveFirst
With rst

If Len(rst!ConstantTask) > 0 Then
strTask = Replace(rst!ConstantTask, " ", "")  ' remove the spaces
strSplit = Split(strTask, ",")
        For i = 0 To UBound(strSplit)
        strUpdate = "UPDATE tblTEMPConAssign SET tblTEMPConAssign.Selected = Yes " & _
            "WHERE tblTEMPConAssign.ConstantTCodeID = '" & CStr(strSplit(i)) & "' "
        CurrentDb.Execute strUpdate, dbFailOnError
        Next i
    End If


If Len(rst!VariableTask) > 0 Then
strTask = Replace(rst!VariableTask, " ", "")  ' remove the spaces
   strSplit = Split(strTask, ",")
        For i = LBound(strSplit) To UBound(strSplit)
        strUpdate = "UPDATE tblTEMPVarAssign SET tblTEMPVarAssign.Selected = Yes " & _
            "WHERE tblTEMPVarAssign.VariableTCodeID = '" & CStr(strSplit(i)) & "' "
        CurrentDb.Execute strUpdate, dbFailOnError
        Next i
End If
    .Close
End With
Set rst = Nothing
End Sub
0
 
mbizupCommented:
Sandra,

I realize that cap answered follow up issues.  Ive been stuck using a cellphone for the past half hour.  But the initial error that you posted this question about was caused by your array being  declared as variant.

(I believe we both contributed to resolving this one)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 9
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now