Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2012
    Posts
    56

    Unanswered: Access 2003 listbox to build SQL string

    Hi All

    I have a multiselect list box that I want to use to build a SQL query ( string )

    Code
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Private Sub CmdUnAssign_Click()

    Dim db As Database
    Dim strSQL As String
    Dim StrCans As String
    Dim StrPrimary As String
    Dim i As Integer

    Set db = CurrentDb

    strSQL = "Delete * From tblCanTracking "

    If Me.ListCanTracking.ItemsSelected.Count = 0 Then
    MsgBox "There are no items Selected", vbOKOnly, "Validate Data"
    Cancel = True
    Exit Sub
    End If

    'Build the IN string by looping through the listbox
    For i = 0 To ListCanTracking.ListCount - 1
    If ListCanTracking.Selected(i) Then

    StrPrimary = StrPrimary & "'" & ListCanTracking.Column(0, i) & "',"
    End If
    ListCanTracking.Selected(i) = False 'clears the selection
    Next i

    'Create the WHERE string, and strip off the last comma of the IN string
    StrPrimary = " WHERE tblCanTracking.Primary in " & "(" & Left(StrPrimary, Len(StrPrimary) - 1) & ") "

    strSQL = strSQL & StrPrimary

    CurrentDb.Execute strSQL, dbFailOnError

    End Sub

    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    The tblCanTracking.Primary field in the where statement is
    and autonumber and I get the error data mismatch. How can I use this field in a string

    Thanks Bob

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So what is the value of strprimary. Its actual value when the code fails. To find that output it to a message box or better yet (if you are seriously using access as opposed to playing with it) learn how to use the debugger. Put a breakpoint on the code and step through it.

    This is a data error, so without an in depth understanding of the application, the schema, the forms its virtualy impossible to diagnose remotely.

    If the column is autoNUMBER then literals should NOT be delimited.

    But learn to use the debugger. Its the only sure fire way of understanding what your code is doing as opoosed to what you think or presume its doing.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2012
    Posts
    56
    Hi thanks for the response. I do use the debugger and I know the field I want to use in my delete query is an autonumber which is a long integer data type and the values are numeric so my problem is when I capture this value in my variable and then put the variable in the string it gets the data mismatch error. I thought that even though it was an integer that when it was in my string variable it would be ok but this is not the case so am looking for idea's to solve the problem.

    Thanks Bob

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are getting
    the error data mismatch
    then its,as the error suggests, a data error

    either you are supplying the wrong datatype for your in clause. that could be because the values are not what you think they are
    OR
    tblCanTracking.Primary is not numeric

    so what is the value of your strPrimary, when the code fails

    Im pretty certain the error is in how you build your list of selected values, but short of seing what the resultant value of strPrimary is its anybody's guess


    the methods you are using to iterate throught the selected items collection isn't one I recognise. not that means very much. just its not a trechnique Ive used.. it may well be perfectly valid
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Oct 2012
    Posts
    56
    Solved:

    Hi all solved problem by using Cstr Function to change AutoNumber field to string

    Code----------------------------------------------------------------------------------------------------

    'Build the IN string by looping through the listbox
    For i = 0 To ListCanTracking.ListCount - 1
    If ListCanTracking.Selected(i) Then
    StrIn = StrIn & CStr(ListCanTracking.Column(0, i)) & ","
    StrCan = StrCan & CStr(ListCanTracking.Column(4, i)) & ","
    End If

    ListCanTracking.Selected(i) = False 'clears the selection

    Next i

    'Create the WHERE string, and strip off the last comma of the IN string
    StrWhere = " WHERE tblCanTracking.Primary in " & "(" & Left(StrIn, Len(StrIn) - 1) & ") "
    StrAssign = " WHERE tblCanLabels.Can_Number in " & "(" & Left(StrCan, Len(StrCan) - 1) & ") "

    strSQL = strSQL & StrWhere
    strSQL1 = strSQL1 & StrAssign

    CurrentDb.Execute strSQL, dbFailOnError

    CurrentDb.Execute strSQL1, dbFailOnError

    ----------------------------------------------------------------------------------------

    Thanks all sometimes when you discuss things someone said mentions something that triggers a thought.

    Cheers Bob

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •