Results 1 to 11 of 11
  1. #1
    KeithSayers is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Apr 2016
    Location
    Canberra, Australia
    Posts
    5

    Using a list box as criteria for a query


    Could SKS help me with something that should be ridiculously simple? I have a form - Choosing State - on which is an unbound list box - StatesList - which contains the acronyms for each of the Australian states and territories. In a query with a State column I have the criteria Forms!Choosing State!StatesList to follow the pattern stated in my Access manual - Forms!FormName!ControlName - but that does not work, the query runs but produces just one blank line. I have tried adding - dot Column(0) - to no avail. I have apparently had this problem before because I have left a note for myself to wrap the whole expression into an Eval() function with double inverted commas around the expression, but that is not helping this time. If someone could put me right I would be most appreciative. I am using Access2003.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    It's best NOT to use spaces in your control names.
    BUT if you do ,put brackets around it...
    forms![my form name]!
    [list box name]

    ALWAYS use the picker tool. It gets the syntax correct.

    Queries can't use .column. If you want to use another column instead of the bound column, then add invisible text boxes. Fill these boxes in the AFTERUPDATE event....
    txtCity = lstBox.column(2)
    TxtCountry= lstBox.column(3)

    Then the query reads the text boxes,not the listbox.

  3. #3
    KeithSayers is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Apr 2016
    Location
    Canberra, Australia
    Posts
    5
    Quote Originally Posted by ranman256 View Post
    It's best NOT to use spaces in your control names.
    BUT if you do ,put brackets around it...
    forms![my form name]!
    [list box name]

    ALWAYS use the picker tool. It gets the syntax correct.

    Queries can't use .column. If you want to use another column instead of the bound column, then add invisible text boxes. Fill these boxes in the AFTERUPDATE event....
    txtCity = lstBox.column(2)
    TxtCountry= lstBox.column(3)

    Then the query reads the text boxes,not the listbox.
    Thank you for the reply but I have covered those two points - there are no spaces in my form and control names and the column reference was something I put in as a try after it failed without, I will try the text box idea but it sees to be roundabout- surely there is some syntax that can go into a query condition to pick up the contents of a list box in a form? Maybe I have picked the wrong forum- I might try another.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by KeithSayers View Post
    ... surely there is some syntax that can go into a query condition to pick up the contents of a list box in a form? ...
    Surely, there is not. A query cannot accept complex data type arguments for its parameters. When you reference a control in a query's parameter, make sure you are referencing its Value property.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can only reference the listbox if it's single-select, not if it's multi-select. If it is, this should work instead of the criteria:

    http://www.baldyweb.com/multiselect.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You can use the listbox column reference in a query IF the listbox is single select and you wrap the column reference in the Eval function. Using the information supplied in the OP:
    Eval("Forms!ChoosingState.StatesList.Column(0)")
    Use of a multi-select listbox (or I suppose, combobox) tends to result in the use of the last-picked item as the criteria.

    I wonder, is this a challenge or anidmadversion
    Maybe I have picked the wrong forum- I might try another.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I forgot about the Eval function. That is definitely a way to reference the column in the query. I wonder if that is a performance hog, though.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If anyone is up to the challenge I suspect both of us would like to know, but I figure the difference would be in milliseconds.
    The reason being that AFAIK, the issue is the Jet Expression Service simply cannot drill down to the control's column level but will happily accept the result of the Eval function as long as it results in a string or numeric value. The difference maker then would be how long it takes to evaluate the value from a valid reference to a column. Again, this value cannot be null, and if it's text the query criteria expression might have to be wrapped in quotes as well. I don't recall because this is one of those tricks we have in our bag but seldom (maybe never) use.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by Micron View Post
    ...this is one of those tricks we have in our bag but seldom (maybe never) use.
    Yeah, Paul suggested the Eval to me back when I was trying to integrate a DAO recordset with an unbound control. Seldom use dynamic parameterized queries and then seldom ask or need such things from our query objects where the Eval function is necessary.

    As for performance, I was thinking along the line of multiple records, where each record must execute the Eval function. Comparing this to referencing a textbox control that already has the value in the computer's memory. Perhaps I am not imagining it correctly.

  10. #10
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Moderator: if this is hijacking the thread, it can be moved to it's own topic and reference this topic from there.
    Perhaps this will suffice:
    Code:
    Public Sub getData()
    Dim dblElapsed As Double
    Dim t As Double, i As Double
    t = GetTickCount 'start timer
    DoCmd.OpenQuery "qry1"
    i = GetTickCount 'get elapsed time to run query
    Debug.Print i - t
    'MsgBox i - t & " - Milliseconds"
    DoCmd.Close acQuery, "qry1"
    End Sub
    I created Excel generated data, 100,000 rows x 6 columns, each column with a different word repeated 100,000 times, then paste appended one column at a time to an Access table. Thus the table has 600,000 rows comprised of 6 unique values. The code is called by AfterUpdate event of a 2 column combo. The timer values are in milliseconds (apparently accurate to only 1/100th of a second) and represent (left to right)
    - running the function using one (same) static criteria value on the query alone 30 times;
    - using the afterupdate event and choosing the same criteria value 30 times
    - random selection of a criteria value from the combo box 30 times. One can expect variations from row to row given that the tasks/services running in a pc background are numerous and fluctuate at any given moment. The only thing that one might conclude is that it takes roughly 7 times longer than static criteria in the query, but the duration is not worth worrying about for most systems IMHO.

    criteria eval same choice eval random choice
    15 63 187
    78 62 343
    78 47 780
    78 31 46
    62 47 468
    78 47 624
    78 47 203
    78 46 483
    78 78 780
    46 63 344
    78 63 624
    47 47 46
    78 47 203
    62 47 484
    78 62 780
    62 47 343
    62 47 765
    62 63 62
    78 63 187
    63 63 343
    63 47 343
    62 47 765
    78 47 499
    63 46 624
    62 62 343
    62 47 483
    78 62 203
    62 62 780
    47 63 780
    62 47 343
    avg. 65.93 avg. 53.66 avg. 441.93

    Oddly enough, evaluating the same combo choice wins out slightly over the query with criteria. Not really a scientific test on the whole, I'll admit.
    Last edited by Micron; 10-03-2016 at 02:21 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I think that I am surprised to see the eval function averaging better times than a referenced control. Perhaps I will not be as apprehensive when considering adding a small user defined function in a query. I wonder if VBA is effecting the control study. Perhaps the Docmd object is causing an effect. I wonder if a QueryDef object instantiated as qry1 would be more direct.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 03-19-2014, 10:40 AM
  2. Replies: 5
    Last Post: 08-02-2012, 09:44 AM
  3. Query Criteria Function and Operator List?
    By samanthaM in forum Access
    Replies: 2
    Last Post: 06-03-2012, 04:52 PM
  4. Multi-Select List Box as Criteria in Query
    By broadwat in forum Queries
    Replies: 6
    Last Post: 09-19-2011, 07:47 AM
  5. Replies: 6
    Last Post: 06-29-2010, 09:56 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums