Solved

Type mismatch when using array from SQL statement

Posted on 2012-03-28
17
446 Views
Last Modified: 2012-03-28
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
Comment
Question by:ssmith94015
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
  • 3
17 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 37778611
Try dropping the parentheses:

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


Try this instead:

strSplit = Split(rst!ConstantTask, ", ")
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37778631
Oh - also dim your array as string:


Dim strSplit() As String
0
 

Author Comment

by:ssmith94015
ID: 37778663
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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:ssmith94015
ID: 37778675
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37778739
If your data includes quotes, change your split to the following:


strSplit = Split(Replace(rst!ConstantTask, chr(39),"") , ", ")
0
 

Author Comment

by:ssmith94015
ID: 37778757
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
 

Author Comment

by:ssmith94015
ID: 37778779
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37778809
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37778931
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
 

Author Comment

by:ssmith94015
ID: 37778990
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
 

Author Comment

by:ssmith94015
ID: 37779003
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
 

Author Comment

by:ssmith94015
ID: 37779023
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 37779029
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
 

Author Comment

by:ssmith94015
ID: 37779032
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37779038
sandra,


just what i was thinking, see my post at http:#a37779029
0
 

Author Closing Comment

by:ssmith94015
ID: 37779107
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37779139
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

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question