Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Sep 2008
    Location
    Virginia, USA
    Posts
    27
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I am using a multi-select list box and the code below to generate a report with records meeting the selected criteria, i.e., the selections made in the list box. The report opens but includes all records from [qry_ClientDiagnoses] instead of just those meeting the selected criteria. The [code] field is text-type data such as "299.01" or "318.0." A msgbox run when the report opens suggests that the strSQL variable is storing the appropriate info, e.g., "[code] = 299.01 or [code] = 318.0"

    Can you tell me how to achieve desired filter?

    Private Sub cmdListBox_Click()

    'Dim strSQL As String (this variable is established in a standard module)
    Dim frm As Form, ctl As Control
    Dim varItem As Variant

    Set ctl = Me.lstCategory
    strSQL = " [code] = "

    For Each varItem In ctl.ItemsSelected
    strSQL = strSQL & ctl.ItemData(varItem) & " OR [code]="
    Next varItem
    strSQL = Left$(strSQL, Len(strSQL) - 11)

    DoCmd.OpenReport "rep_ClientDiagnoses", acViewPreview, , Chr(34) & strSQL & Chr(34)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Instead of surrounding the entire string in quotes, you must place quotes around each value.

    [code]Private Sub cmdListBox_Click()
    'Dim strSQL As String (this variable is established in a standard module)
    Dim frm As Form, ctl As Control
    Dim varItem As Variant

    Set ctl = Me.lstCategory
    strSQL = "[code]="

    For Each varItem In ctl.ItemsSelected
    strSQL = strSQL & Chr(34) & ctl.ItemData(varItem) & Chr(34) & " OR
    Code:
    ="
      Next varItem
      strSQL = Left$(strSQL, Len(strSQL) - 11)
    
      DoCmd.OpenReport "rep_ClientDiagnoses", acViewPreview, , strSQL
    End Sub

  3. #3
    Lounger
    Join Date
    Sep 2008
    Location
    Virginia, USA
    Posts
    27
    Thanks
    2
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='790896' date='27-Aug-2009 05:28']Instead of surrounding the entire string in quotes, you must place quotes around each value.

    [code]Private Sub cmdListBox_Click()
    'Dim strSQL As String (this variable is established in a standard module)
    Dim frm As Form, ctl As Control
    Dim varItem As Variant

    Set ctl = Me.lstCategory
    strSQL = "[code]="

    For Each varItem In ctl.ItemsSelected
    strSQL = strSQL & Chr(34) & ctl.ItemData(varItem) & Chr(34) & " OR
    Code:
    ="
      Next varItem
      strSQL = Left$(strSQL, Len(strSQL) - 11)
    
      DoCmd.OpenReport "rep_ClientDiagnoses", acViewPreview, , strSQL
    End Sub
    [/quote]

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Er, your reply only quotes mine, without adding anything...

Posting Permissions

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