Using Multi select Listbox as criteria for SQL statement 1

[PROXI]

If I make only one selection from the listbox the SQL works perfectly. If I make multiple selections, it pulls everything (I.E. As if you didn't put in any criteria for strWhere). Any help would be really appreciated.

Private Sub CmdExport_Click()
On Error GoTo Err_CmdExport_Click

    Dim stDocName As String
    Dim varItem As Variant
    Dim strWhere As String
    Dim strSQL As String

'Get list of PPA's from the listbox on FrmMain
For Each varItem In Me![LstbxPPA].ItemsSelected
strWhere = strWhere _
& Chr(34) & Me![LstbxPPA].Column(0, varItem) & Chr(34) & " Or "
Next varItem

strWhere = Left(strWhere, Len(strWhere) - 4) 'Remove the last " Or "


'Clear PPAInvoice table
DoCmd.OpenQuery "ClearPPAInvoices", acViewNormal, acEdit

'Repopulate table with selected information
        strSQL = "INSERT INTO TblPPAInvoices ( PPA_NBR, CASE_NBR, INV_NBR, INV_DATE, AMOUNT, INV_BALANCE, ACCR_DATE, CASE_NAME ) " & _
                "SELECT c.PPA_NBR AS PPA_NBR, c.CASE_NBR AS CASE_NBR, c.INV_NBR AS INV_NBR, c.INV_DATE AS INV_DATE, b.AMOUNT AS AMOUNT, c.INV_BALANCE AS INV_BALANCE, b.ACCR_DATE AS ACCR_DATE, a.CASE_NAME AS CASE_NAME " & _
                "FROM PPAB_PPAB_CASES a, " & _
                "PPAB_PPAB_INVOICED_CHARGES b, " & _
                "PPAB_PPAB_INVOICES c " & _
                "WHERE (a.CASE_NBR = b.CASE_NBR) " & _
                "AND (a.PPA_NBR = b.PPA_NBR) " & _
                "AND (b.INV_NBR = c.INV_NBR) " & _
                "AND (c.PPA_NBR =" & strWhere & ") " & _
                "AND (c.INV_BALANCE<>0) " & _
                "AND (c.PERSON_TYPE ='PA') " & _
                "ORDER BY c.PPA_NBR, c.CASE_NBR; "


DoCmd.RunSQL strSQL

    stDocName = "TblPPAInvoices"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, stDocName, "C:\Documents and Settings\" & (Environ$("Username")) & "\Desktop\Detail Aging Report", True

Exit_CmdExport_Click:
    Exit Sub

Err_CmdExport_Click:
    MsgBox Err.Description
    Resume Exit_CmdExport_Click
    
End Sub

Thanks,

PROXI

 

[dhookom]

Your where might resolve like:

  [Color] = "Green" OR "Red" OR "Blue"

It should be either:

  [Color] IN( "Green" , "Red" , "Blue" )

Or

  [Color] = "Green" OR [Color] = "Red" OR [Color] = "Blue"

Duane
Hook'D on Access
MS Access MVP

 

[PROXI]

Can you please elaborate on what you mean. I am unsure where to alter the code to get to those results.

Thanks,

PROXI

 

[dhookom]

Try something like:

Private Sub CmdExport_Click()
On Error GoTo Err_CmdExport_Click

    Dim stDocName As String
    Dim varItem As Variant
    Dim strWhere As String
    Dim strSQL As String

'Get list of PPA's from the listbox on FrmMain
'  assumes PPA is numeric
For Each varItem In Me![LstbxPPA].ItemsSelected
strWhere = strWhere _
& Chr(34) & Me![LstbxPPA].Column(0, varItem) & Chr(34) & " , "
Next varItem

strWhere = Left(strWhere, Len(strWhere) - 3) 'Remove the last " , "


'Clear PPAInvoice table
DoCmd.OpenQuery "ClearPPAInvoices", acViewNormal, acEdit

'Repopulate table with selected information
        strSQL = "INSERT INTO TblPPAInvoices ( PPA_NBR, CASE_NBR, INV_NBR, INV_DATE, AMOUNT, INV_BALANCE, ACCR_DATE, CASE_NAME ) " & _
                "SELECT c.PPA_NBR AS PPA_NBR, c.CASE_NBR AS CASE_NBR, c.INV_NBR AS INV_NBR, c.INV_DATE AS INV_DATE, b.AMOUNT AS AMOUNT, c.INV_BALANCE AS INV_BALANCE, b.ACCR_DATE AS ACCR_DATE, a.CASE_NAME AS CASE_NAME " & _
                "FROM PPAB_PPAB_CASES a, " & _
                "PPAB_PPAB_INVOICED_CHARGES b, " & _
                "PPAB_PPAB_INVOICES c " & _
                "WHERE (a.CASE_NBR = b.CASE_NBR) " & _
                "AND (a.PPA_NBR = b.PPA_NBR) " & _
                "AND (b.INV_NBR = c.INV_NBR) " & _
                "AND (c.PPA_NBR IN (" & strWhere & ") ) " & _
                "AND (c.INV_BALANCE<>0) " & _
                "AND (c.PERSON_TYPE ='PA') " & _
                "ORDER BY c.PPA_NBR, c.CASE_NBR; "


DoCmd.RunSQL strSQL

    stDocName = "TblPPAInvoices"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, stDocName, "C:\Documents and Settings\" & (Environ$("Username")) & "\Desktop\Detail Aging Report", True

Exit_CmdExport_Click:
    Exit Sub

Err_CmdExport_Click:
    MsgBox Err.Description
    Resume Exit_CmdExport_Click
    
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[PROXI]

You are my hero. It worked like a charm!

Thanks,

PROXI