Solved

Type mismatch when using array from SQL statement

Posted on 2012-03-28
17
448 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

726 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