Compiling a filter from List Box values

[KristenAsh]

Hi there.
I created a database a long time ago (in Access 97) and over the years it has been upgraded through the versions and now lives in Access 2010. This conversion was done some time ago and we have just found some VBA code that doesn't seem to be working anymore. It's been a while since I've been in this database so I'm a little rusty and hope that someone can point me in the right direction.

I have a form with four multi-select list boxes. The user highlights information in each list box (it can be one box or many) and then clicks a command button to run the code to put all the selections together to form a filter string for a report to open on. My code is no longer pulling the correct information and I can't figure out why. In most cases the report is opening but has the wrong number of records. In some cases, no data is found even though it should. Is there something in this code that no longer works in Access 2010? Any help would be greatly appreciated! The code is below.



Private Sub Query_Click()
On Error GoTo Err_Query_Click

Dim stDocName As String
Dim stDocName2 As String
Dim stDocName3 As String
Dim stDocName4 As String
Dim strfilter As String
Dim strFilter2 As String
Dim strFilter3 As String
Dim strFilter4 As String
Dim masterStrFilter As String
Dim intIndex As Variant
Dim ssql As String
Dim ssql2 As String
Dim ReportName As String
Dim mcrExportRptCriteria As String
Dim FileName As String
strfilter = 0
strFilter2 = 0
strFilter3 = 0
strFilter4 = 0
masterStrFilter = 0


'creates WHERE clause for Plant_Name field (strFilter).
strfilter = ""
intIndex = 0
For Each intIndex In Plant.ItemsSelected
stDocName = Plant.Column(1, intIndex)

If strfilter = "" Then
strfilter = "Plant_Name = '" & stDocName & "'"
Else
strfilter = strfilter & " or Plant_Name ='" & stDocName & "'"
End If
Next intIndex

'creates WHERE clause for Type_Test field (strFilter2).
strFilter2 = ""
intIndex = 0
For Each intIndex In Type_Test.ItemsSelected
stDocName2 = Type_Test.Column(0, intIndex)

If strFilter2 = "" Then
strFilter2 = "Type_Test = '" & stDocName2 & "'"
Else
strFilter2 = strFilter2 & " or Type_Test ='" & stDocName2 & "'"
End If
Next intIndex

'creates WHERE clause for Product_Code field (strFilter3).
strFilter3 = ""
intIndex = 0
For Each intIndex In Product_Code.ItemsSelected
stDocName3 = Product_Code.Column(1, intIndex)

If strFilter3 = "" Then
strFilter3 = "Product_Code = " & stDocName3
Else
strFilter3 = strFilter3 & " or Product_Code =" & stDocName3 & "'"
End If
Next intIndex

'creates WHERE clause for Year_Produced field (strFilter4).
strFilter4 = ""
intIndex = 0
For Each intIndex In Year_Produced.ItemsSelected
stDocName4 = Year_Produced.Column(0, intIndex)

If strFilter4 = "" Then
strFilter4 = "Year_Produced = '" & stDocName4 & "'"
Else
strFilter4 = strFilter4 & " or Year_Produced='" & stDocName4 & "'"
End If
Next intIndex

masterStrFilter = ""
If strfilter <> "" Then
masterStrFilter = "(" & strfilter & ")"
End If

If strFilter2 <> "" Then
If masterStrFilter = "" Then
masterStrFilter = strFilter2
Else
masterStrFilter = masterStrFilter & " and " & "(" & strFilter2 & ")"
End If
End If


If strFilter3 <> "" Then
If masterStrFilter = "" Then
masterStrFilter = strFilter3
Else
masterStrFilter = masterStrFilter & " and " & "(" & strFilter3 & ")"
End If
End If


If strFilter4 <> "" Then
If masterStrFilter = "" Then
masterStrFilter = strFilter4
Else
masterStrFilter = masterStrFilter & " and " & "(" & strFilter4 & ")"
End If
End If

ReportName = "rptDataQuery"
FileName = "\\Whitemarshnt\qa\Test Report\DataQuery\Data Query Report.xls"
'-----------------
If masterStrFilter = "" Then
MsgBox ("You must select some criteria.")
Else

DoCmd.OpenReport ReportName, acViewPreview, , masterStrFilter
DoCmd.OutputTo acOutputReport, ReportName, acFormatXLS, FileName, True


'------------------
Err_Query_Click:
If Err.Number = 2427 Then

MsgBox ("Your query produced no results. Please select different criteria.")
DoCmd.Close acReport, "rptDataQuery"
End If
End If
DoCmd.Close acReport, ReportName
End Sub

[Rod]

Hi there,

At first glance all looks OK. I don't think there has been any change to multi-select across the various versions to cause these problems. Have you tried putting a message box or Debug.Print immediately before the DoCmd.OpenReport statements? At least you can see what WHERE clause has been built; this may give you a clue where the problem lies.

How much time do you want to spend on this? My immediate reaction is to ask why you are not using the IN() construct; it's much neater than long constructs of, "Plant_Code = 1 OR Plant_Code = 3 OR Plant_Code = 10." Also you can use string concatenation with null propagation which avoids all those If = "" Then/Else/End If chunks of code.

[KristenAsh]

Rod,
Thank you for your quick reply. I've been staring at this for 3/5 hours now and don't know what's going on.
I put the Debut.Print statement in and it appears to be pulling the information correctly but there is still data missing (An example is below).
(Plant_Name = 'Lilesville' or Plant_Name ='Conley') and (Type_Test = 'V') and (Product_Code = 12160 or Product_Code =13160')

Maybe it's not passing to the report correctly? I ran it on simply (Plant_Name = 'Lilesville') and came up 15 short on the report as compared to just pulling up the table and filtering. I opened the report and put (Plant_Name = 'Lilesville') in the filter box and it ran without the filter.

I'm not using the IN()construct because I don't know how. If it works better I'm definitely willing to change it. Could you show me an example that and the string concatenation. I may be able to figure it out if you point me in the right direction. I just really need to get this thing working this weekend.

Thank you again for your help!

Kristen

[Rod]

Maybe it's not passing to the report correctly? I ran it on simply (Plant_Name = 'Lilesville') and came up 15 short on the report as compared to just pulling up the table and filtering. I opened the report and put (Plant_Name = 'Lilesville') in the filter box and it ran without the filter.

That's weird. I think your code's OK. Any reason you're using plant names rather than numbers?

I'll knock up something for you regarding my suggestions but I can't help thinking your problem lies elsewhere.

[KristenAsh]

Rod,
I think I figured out my problem! It looks like there might be a problem with the underlying query for the report.
I'm still curious about the IN() construct and sting concatenation, so if it's not to much trouble I'd still like to see an example.
Thank you so much for your help! I really do appreciate your time.

Kristen

[Rod]

Glad you seem to have found it.

I have spotted what I think is an error in your published code:

    If strFilter3 = "" Then
           strFilter3 = "Product_Code = " & stDocName3
    Else
           strFilter3 = strFilter3 & " or Product_Code =" & stDocName3 & "'"
    End If
    Next intIndex

Assuming that Product_Code is numeric then the extra & "'" is not needed at the end of the fourth line - in fact it may cause an error.

Now here's my effort. It's untested! I think that Access DoCmd understands the IN() construct - SQL certainly does. In essence you can code something like: Plant_Name IN('Lilesville','Conley'). Also I have used the concatenation with null propagation - it's symbol is a plus sign. It only works with variants but what it does is that if either of the variables each side of the plus sign is null then the result is null - very useful for first time conditions. In every case the comma is omitted the first time round as varString is null.

Dim varString As Variant
    
    'creates WHERE clause for Plant_Name field (strFilter).
    
    varString = Null
    For Each intIndex In Plant.ItemsSelected
        varString = varString + "," & "'" & Plant.Column(1, intIndex) & "'"
    Next
    If Not IsNull(varString) Then strfilter = "Plant_Name IN(" & CStr(varString) & ")"
    
    'creates WHERE clause for Type_Test field (strFilter2).
    
    varString = Null
    For Each intIndex In Type_Test.ItemsSelected
        varString = varString + "," & "'" & Type_Test.Column(0, intIndex) & "'"
    Next
    If Not IsNull(varString) Then strFilter2 = "Type_Test In(" & CStr(varString) & ")"
    
    'creates WHERE clause for Product_Code field (strFilter3).
    
    varString = Null
    For Each intIndex In Product_Code.ItemsSelected
        varString = varString + "," & Product_Code.Column(1, intIndex)
    Next
    If Not IsNull(varString) Then strFilter3 = "Product_Code IN(" & CStr(varString) & ")"
    
    'creates WHERE clause for Year_Produced field (strFilter4).
    
    varString = Null
    For Each intIndex In Year_Produced.ItemsSelected
        varString = varString + "," & "'" & Year_Produced.Column(0, intIndex) & "'"
    Next
    If Not IsNull(varString) Then strFilter4 = "Year_Produced In(" & CStr(varString) & ")"

[KristenAsh]

Rod,
Thanks so much for your help. I will try this out. It's much cleaner than what I'm using.
I really appreciate you assistance!
Kristen

[Rod]

Hi Kristen

I could improve it even a little further! If I have time I'll rewrite the complete Sub procedure. Let's see how few lines are needed. Good luck!

[Rod]

Thirty nine lines including dimension but excluding declaration and labels.

Private Sub Query_Click()

Dim varWhereString As Variant
Dim varString As Variant
Dim intIndex As Integer
Dim strReportName As String
Dim strFileName As String

On Error GoTo Err_Query_Click

varWhereString = Null

'creates WHERE clause for Plant_Name field (strFilter).

varString = Null
For Each intIndex In Plant.ItemsSelected
varString = varString + "," & "'" & Plant.Column(1, intIndex) & "'"
Next
varWhereString = "Plant_Name IN(" + varString + ")"

'creates WHERE clause for Type_Test field (strFilter2).

varString = Null
For Each intIndex In Type_Test.ItemsSelected
varString = varString + "," & "'" & Type_Test.Column(0, intIndex) & "'"
Next
varWhereString = varWhereString + " AND " & "Type_Test In(" + varString + ")"

'creates WHERE clause for Product_Code field (strFilter3).

varString = Null
For Each intIndex In Product_Code.ItemsSelected
varString = varString + "," & Product_Code.Column(1, intIndex)
Next
varWhereString = varWhereString + " AND " & "Product_Code IN(" + varString + ")"

'creates WHERE clause for Year_Produced field (strFilter4).

varString = Null
For Each intIndex In Year_Produced.ItemsSelected
varString = varString + "," & "'" & Year_Produced.Column(0, intIndex) & "'"
Next
varWhereString = varWhereString + " AND " & "Year_Produced In(" + varString + ")"

If IsNull(varWhereString) Then MsgBox "You must select some criteria.": GoTo Exit_Procedure
strReportName = "rptDataQuery"
strFileName = "\\Whitemarshnt\qa\Test Report\DataQuery\Data Query Report.xls"
DoCmd.OpenReport ReportName, acViewPreview, , CStr(Nz(varWhereString, ""))
DoCmd.OutputTo acOutputReport, ReportName, acFormatXLS, FileName, True

Exit_Procedure:
Exit Sub

Err_Query_Click:

If Err.Number = 2427 Then
MsgBox ("Your query produced no results. Please select different criteria.")
DoCmd.Close acReport, "rptDataQuery"
DoCmd.Close acReport, ReportName
End If
Resume Exit_Procedure

End Sub


I've left it en clair because I hate the way the code tags destroy the formatting. This is not tested so beware.