Solved

Type mismatch when using array from SQL statement

Posted on 2012-03-28
17
441 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
  • 9
  • 5
  • 3
17 Comments
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
Comment Utility
Oh - also dim your array as string:


Dim strSplit() As String
0
 

Author Comment

by:ssmith94015
Comment Utility
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
 

Author Comment

by:ssmith94015
Comment Utility
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
Comment Utility
If your data includes quotes, change your split to the following:


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

Author Comment

by:ssmith94015
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
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
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
sandra,


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

Author Closing Comment

by:ssmith94015
Comment Utility
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
Comment Utility
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now