Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Type mismatch when using array from SQL statement

Posted on 2012-03-28
17
Medium Priority
?
456 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
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

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 2000 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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

581 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