Results 1 to 12 of 12
  1. #1
    RayMilhon is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,066

    Using a Listbox to build criteria for a query

    I have a form that allows the user to select a date range and generate a report. The report is based on a query that has 3 criteria. One of those is built by using a multi select list box for categories. I have a function that builds a string out of the selected Categories. the categories are from a table that contains each of the Hedis measures we are tracking. The report is designed to print mailing labels to those members in the denominator but not in the numerator for the measure. Depending on when the report is run certain measures are focused on so the listbox is multi-select so the user can select which measures the mailing is going out on.



    If I type in the measures on the criteria line: In ("BMI","A1c") the query works fine. If I use the function to build the criteria from those items selected in the listbox I get 0 records. In (critstr()) where critstr = "BMI","A1c"

    here's the function

    Code:
    Public Function critstring() As String
    
        Dim varitm As Variant
        For Each varitm In Forms!frm_reports.lst_SubMeasure.ItemsSelected
            critstring = critstring & """" & Forms!frm_reports.lst_SubMeasure.ItemData(varitm) & ""","
        Next varitm
        critstring = Left(critstring, Len(critstring) - 1)
    
    End Function
    If I add critstring() to one of the columns I get exactly what I expect. What am I missing about the IN ()

  2. #2
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    I would guess its with how you are referencing the data from within the loop.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I see nothing wrong with the syntax. You have the function in a standard module, correct? It can't be on the form AFAIK.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    have you tried referencing the object per
    https://msdn.microsoft.com/EN-US/lib.../ff823015.aspx

    My Concern is where you are Casting an Object into a Variant than then Using that Variant to reference an Object of an Collection.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not sure what you mean. The itemsSelected collection is a property of a list box or combo box, and it has to be declared as a variant. We haven't seen yet whether or not the function is in a standard module. If not, the query will not get anything from the function when it runs.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    RayMilhon is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,066
    The function is in a standard module. If I put the function in one of the columns of the query I get the expected results. It's when I put the function in the criteria section using

    In (critstring()) Where critstring() = "BMI","A1c"

  7. #7
    RayMilhon is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,066
    Update- Tried a couple of different things. If you notice on the code above I included the quotes for the string. If I remove those and only select 1 item the report works. If I select more than 1 item it fails. Any ideas? For now I'm limiting the listbox to 1 item. But that's a temporary fix. Eventually they will need to include multiple items so any help is greatly appreciated.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Ray,

    Not sure what you have, but the vba should be parsing the ItemsSelected and build a
    string such as IN("BMI","A1c")

    and this would be the criteria for a where clause.

    Here's a sample from a form I have. I have selected a supervisor and the listbox identifies the Employees he/she supervises. You can select several items from the listbox.
    The code below loops through the list box's ItemsSelected and creates the info for a where clause. In my case the Where clause limits a Report to these Employees.
    I have added DEbug.Print statements to show the value of sWho on each pass of the loop, and a debug.print to show the final In () clause.

    button click code
    Code:
    Private Sub Command4_Click()
          Dim vItem As Variant
          Dim sWho As String
          Dim sIn As String
    
    10    sWho = ""
    20    sIn = ","
          
    30    For Each vItem In Me.List2.ItemsSelected
    40     sWho = sWho & Me.List2.ItemData(vItem)
    50     sWho = sWho & sIn
           Debug.Print sWho
    60    Next vItem
    70    sWho = Mid(sWho, 1, Len(sWho) - 1) 'get rid of trailing comma
    80     sSQL = "empid IN (" & sWho & ")"
           Debug.Print sSQL
    90    DoCmd.OpenReport "Employee", acViewPreview, , sSQL
    End Sub
    Output of the Debug.print statements. In my case the ItemsSelected were numbers.

    Code:
    2,
    2,3,
    2,3,8,
    2,3,8,9,
    empid IN (2,3,8,9)

  9. #9
    InsuranceGuy is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Location
    Colorado
    Posts
    126
    Quote Originally Posted by RayMilhon View Post
    Update- Tried a couple of different things. If you notice on the code above I included the quotes for the string. If I remove those and only select 1 item the report works. If I select more than 1 item it fails. Any ideas? For now I'm limiting the listbox to 1 item. But that's a temporary fix. Eventually they will need to include multiple items so any help is greatly appreciated.
    Unfortunately, yes, I've run into this before.

    Your function is returning a string. ONE string. It looks like this:
    Code:
    "BMI","A1c"
    Yep, one string, 11 characters long.

    It is interpreted by the query engine as:

    [fieldname] in ("""BMI"",""A1c""")

    So clearly you won't get any desired results this way.


    If you don't have a lot of entries in your list box, you can reverse the comparison--you just need to add some handling for the quotation marks like this:

    Code:
    critstring() like "*""" & [RecordFieldName] & """*"
    It looks like this:
    Click image for larger version. 

Name:	Untitled picture.png 
Views:	30 
Size:	8.1 KB 
ID:	23588


    You could also modify the SQL string using a QueryDef object. That's usually what I do, but it's more complicated.


    Best of luck. Cheers,


    Jeff

  10. #10
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I would try replacing the double with single quotes (I'm sure that will work in a manually written IN string), and if necessary, build the entire thing, including the IN clause, in the string returned by the function.

  11. #11
    RayMilhon is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,066
    Nothing worked. For the moment the user's are ok with selecting one option at a time. So I'll live with that for now but will look at it again when I have more time. Thanks for everyone's suggestions.

  12. #12
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Have been away for almost a month, hence the late addition. After further consideration of your comment "If I add critstring() to one of the columns I get exactly what I expect." The Jet expression service can return the result of a function as a calculated field (per your comment) OR as criteria if the result is the correct data type for the field and is properly formatted. If your function call is entered into the query criteria row as critstring() and it returns a single word without quotes, it will work (tried and proven). However, the suggestions here attempt to pass the results of a user defined function to a built in function (IN), which I think is not possible. Or the suggestion to create the criteria in a way that resembles a criteria row entry: IN('text1', 'text2", 'text3') is in fact returned as "IN('text1', 'text2", 'text3')" which will not work either.

    I think your only option is to follow the advice given in post #8 (which supplies a filter to the report opening) or construct the entire sql and open the report based on that.

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

Similar Threads

  1. Build Query
    By Kyoshikawa in forum Queries
    Replies: 7
    Last Post: 10-06-2015, 10:03 PM
  2. Replies: 4
    Last Post: 09-23-2014, 08:39 AM
  3. Replies: 2
    Last Post: 03-23-2014, 06:50 AM
  4. Replies: 4
    Last Post: 02-14-2013, 09:33 PM
  5. Query criteria retrieved from a Form's listbox
    By blacksaibot in forum Programming
    Replies: 1
    Last Post: 01-29-2010, 10:38 AM

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