Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2014
    Posts
    9

    Unanswered: query prompt for array

    I have a table in Access 2010 where there is Column [Ticket_Number] and an attempting to to build a form where a text box will take an array as a query filter.

    Basically I want the user to enter the tickets to search for separated in by a comma in object Text49, hit enter, and then the form updates to only show the tickets that were entered into Text49.

    No matter what is done I get random errors. Any help would be greatly appreciated for this less than advanced guy.

    **Update**
    I got the following code to return a single ticket but in the loop it continually prompts for the next ticket number and only returns a single record in the form, when i would prefer it to return all the tickets in the array.

    ticket1,ticket2,ticket3,ticket20 and so on.

    Code:
    Private Sub Command51_Click()
    Dim strInput As String
    Dim arrParam As Variant
    Dim strSQL As String
    Dim i As Integer
    
    'assign the text in your text box to this variable
    strInput = Me.Text49.Value
    
    'splits the string when it encounters a comma, assigns to array
    'this only works if the parameters are always separated by commas!
    arrParam = Split(strInput, Chr(44))
    
    
    'loops through the array and appends each value in the array to the where clause of the query SQL
    'and runs the query.
    For i = 0 To UBound(arrParam)
        
        'Build the query you need.
       'The trim is in there to get rid of leading and   trailing spaces
       
        DoCmd.ApplyFilter "Tickets_Update", "[Ticket_Number]='" & Trim(arrParam(i)) & "'", ""
        
        ' "Select * FROM Multi_Update_Query WHERE [Ticket_Number]=" & Trim(arrParam(i)), ""
    
    Next
    
    End Sub
    Last edited by mike760534211; 08-02-14 at 12:38. Reason: got some form of working code

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Instead of splitting the criteria and using a loop, use the IN operator:
    Code:
    Dim strList as String
    Dim strSQL As String
    
    strList = Replace(Me.Text49.Value, " ", "")
    strSQL = "SELECT * FROM Multi_Update_Query WHERE Ticket_Number IN (" & strList & ");"
    If Ticket_Number is not numeric, things become more complex:
    Code:
    Dim strList as String
    Dim strSQL As String
    Dim varList as Variant
    Dim i As Long
    
    strList = Replace(Me.Text49.Value, " ", "")
    varList = Split(strList, ",")
    For i = 0 To Ubound(varList)
        varList(i) = "'" & varList(i) & "'"
    Next i
    strList = Join(varList, ",")
    strSQL = "SELECT * FROM Multi_Update_Query WHERE Ticket_Number IN (" & strList & ");"
    Have a nice day!

  3. #3
    Join Date
    Aug 2014
    Posts
    9
    Since my ticket numbers are alpha numeric I went with option 2. After putting in the array 6420535,6544613,6554914,6560031,INC0000597609 it doesn't update the form or do anything.

    The DB table is Record_Store and the form is based on query Multi_Update_Query. The forms name is Multi_Update_Form.

    The reason I am basing the form on a query is the query already filters out all tickets that are closed.

    Quote Originally Posted by Sinndho View Post
    Instead of splitting the criteria and using a loop, use the IN operator:

    If Ticket_Number is not numeric, things become more complex:

    Code:
    Dim strList as String
    Dim strSQL As String
    Dim varList as Variant
    Dim i As Long
    
    strList = Replace(Me.Text49.Value, " ", "")
    varList = Split(strList, ",")
    For i = 0 To Ubound(varList)
        varList(i) = "'" & varList(i) & "'"
    Next i
    strList = Join(varList, ",")
    strSQL = "SELECT * FROM Multi_Update_Query WHERE Ticket_Number IN (" & strList & ");"

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Print the contents of strSQL in the immediate windows:
    Code:
    strList = Join(varList, ",")
    strSQL = "SELECT * FROM Multi_Update_Query WHERE Ticket_Number IN (" & strList & ");"
    Debug.Print strSQL
    Stop
    2. When the code stops, open the immediate windows (Ctrl+G), then copy the contents of strSQL (select then Ctrl+C)

    3. Create a new query, switch to SQL view and paste what you copied in 2 (Ctrl+V)

    4. Try switching the query to Datasheet view and see what happens.
    Have a nice day!

  5. #5
    Join Date
    Aug 2014
    Posts
    9
    Follwoing the directions all it shows in the imediate windows is:

    Code:
    SELECT * FROM Record_Store WHERE Ticket_Number IN ();
    When running in a new query in SQL and Datasheet view if gives popup error:
    Reserved error (-3201); there is no message for this error.


    Quote Originally Posted by Sinndho View Post
    1. Print the contents of strSQL in the immediate windows:
    Code:
    strList = Join(varList, ",")
    strSQL = "SELECT * FROM Multi_Update_Query WHERE Ticket_Number IN (" & strList & ");"
    Debug.Print strSQL
    Stop
    2. When the code stops, open the immediate windows (Ctrl+G), then copy the contents of strSQL (select then Ctrl+C)

    3. Create a new query, switch to SQL view and paste what you copied in 2 (Ctrl+V)

    4. Try switching the query to Datasheet view and see what happens.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It seems obvious that threre is nothing in strList:
    Code:
    SELECT * FROM Record_Store WHERE Ticket_Number IN ();
    -Are there comma-separated values in Text49?
    -What's in strList (Debug.Print...) before and after the processing of its contents through varList?
    Have a nice day!

  7. #7
    Join Date
    Aug 2014
    Posts
    9
    The data is Text49 are comma separated. the string I am searching for is : 6420535,6544613,6554914,6560031,ACSI1693155,INC000 005830985 as I know these are in the database.

    Before I hit the search button the Immediate window is blank, and after I hit the search button it only shows what I posted earlier.

    Quote Originally Posted by Sinndho View Post
    It seems obvious that threre is nothing in strList:
    Code:
    SELECT * FROM Record_Store WHERE Ticket_Number IN ();
    -Are there comma-separated values in Text49?
    -What's in strList (Debug.Print...) before and after the processing of its contents through varList?

  8. #8
    Join Date
    Aug 2014
    Posts
    9
    I was able to get
    Code:
    strSQL = "SELECT * FROM Record_Store WHERE Ticket_Number IN (" & strList & ");"
    to populate with the correct information below in the Immediate window by switching
    Code:
    varList = Split(strList, ",")
    strList = Replace(Me.Text49.Value, " ", "")
    with
    Code:
    strList = Replace(Me.Text49.Value, " ", "")
    varList = Split(strList, ",")
    and it returns the following value in the Immediate window with does work in an SQL query.
    Code:
    SELECT * FROM Multi_Update_Query WHERE Ticket_Number IN ('6420535','6544613','6554914','6560031','ACSI1693155','INC000005830985');
    Now the only issue is that the form doesn't update reflecting the search string.

    Quote Originally Posted by Sinndho View Post
    It seems obvious that threre is nothing in strList:
    Code:
    SELECT * FROM Record_Store WHERE Ticket_Number IN ();
    -Are there comma-separated values in Text49?
    -What's in strList (Debug.Print...) before and after the processing of its contents through varList?

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by mike760534211 View Post
    Now the only issue is that the form doesn't update reflecting the search string.
    You must use strSQL as the RecordSource property of the form:
    Code:
    strSQL = "SELECT * FROM Multi_Update_Query WHERE Ticket_Number IN (" & strList & ");"
    Me.RecordSource = strSQL
    Have a nice day!

  10. #10
    Join Date
    Aug 2014
    Posts
    9
    that worked great. thanks a ton. i have been messing with this for weeks.

    Quote Originally Posted by Sinndho View Post
    You must use strSQL as the RecordSource property of the form:
    Code:
    strSQL = "SELECT * FROM Multi_Update_Query WHERE Ticket_Number IN (" & strList & ");"
    Me.RecordSource = strSQL

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  12. #12
    Join Date
    Aug 2014
    Posts
    9
    just a quick question. not a requirement. can the comma be changed to a carriage return. - already figured it out
    Quote Originally Posted by Sinndho View Post
    You're welcome!
    Last edited by mike760534211; 08-04-14 at 18:06. Reason: already solved this one....

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by mike760534211 View Post
    just a quick question. not a requirement. can the comma be changed to a carriage return.
    In you mean when entering the values in the TextBox, yes it is, provided that you use:
    Code:
    varList = Split(strList, vbNewLine)
    When creating the IN list, the SQL syntax requires that a comma be used as the list separator, so you cannot change:
    Code:
    strList = Join(varList, ",")
    Have a nice day!

Posting Permissions

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