# Comments

Comment:
This comment contains two double quotes in a row: ""


Comment:
This comment contains a single quote in double quotes: "'"


Comment:
This comment contains a single quote ' but no double quotes.


# Customers

Address:
Carrera 22 con Ave. Carlos Soublette #8-35


Address:
Carrera 52 con Ave. Bolívar #65-98 Llano Largo


# tblDescription

text:
GENERIC CODE TO BUILD REPORT FILTERS
Access 2000, 97,95

I developed a report wizard interface where the user first picks one of many standard reports from a combo box and can then filter the report based on selections made in any of ten different list boxes.  Each list box can have none, one, or many selections made by the user. (For example, from a list box of States, the user could pick one state, several states, or none at all.)

Instead of building code for each individual list box I wanted a generic function that would build IN statements from all the list boxes and patch them together for the “wherecondition” sent to the filter of the report.  I also wanted a text string that prints in the report header of exactly what the user selected.

Here’s how I accomplished the task.

First, in a module I declared the following global variables
Public gstrFinalBuild As String
Public gstrReportCriteria As String
The “gstrFinalBuild” holds the final IN statement for the “wherecondition” sent to the filter of the report. 
The “gstrReportCriteria” holds the final text statement for a text box in the report header that prints the users selections.

After the user has selected the report they want and made all of the selections in the list boxes, they click a button that processes their selections and previews the report.  This button has the following code.

Private Sub BuildandPreviewReport_Click()
Dim strReport As String
gstrFinalBuild = "" ‘initialize to nothing
gstrReportCriteria = "" ‘initialize to nothing


Call ListBoxBuilder("lstBox1", "FieldName1", "ReportText1", ColumnNumber(forFilter), ColumnNumber(forText))
Call ListBoxBuilder("lstBox2", "FieldName2", "ReportText2", ColumnNumber(forFilter), ColumnNumber(forText))
. . . .
etc for each listbox 
. . . .

gstrFinalBuild = Mid([gstrFinalBuild], 1, Len(gstrFinalBuild) - 3) ‘ remove the final AND from the filter
gstrReportCriteria = Mid([gstrReportCriteria], 1, Len(gstrReportCriteria) - 4) ‘ remove the final AND from the text string
strReport = Me.cboReportName ‘get the report name from the combo box
DoCmd.OpenReport strReport, acPreview, , gstrFinalBuild ‘open the report in preview mode with the filter
End Sub


The Control Source of a text box in the report header has the following statement “=GetReportCriteria()”. This retrieves the text string to print that shows the users selections 
   Function GetReportCriteria()
       GetReportCriteria = gstrReportCriteria
   End Function

The heart of the reusable code is because five parameters are sent to the ListBoxBuilder function. They are:
1) the name of the list box
2) the name of the field in the reports recordset that will be used to filter on (ie [PROVINCE_OR_STATE_CODE])
3) the text string that will be used to print the users selection on the report (ie. States: )
4) the column number in the list box that holds the field information if the IN statement (ie CA)
5) the column number in the list box that holds the information for the text string of the users selections (ie. California)
The reason for the different column numbers in item 4 and 5 above is to produce a smaller IN statement.  The maximum length of the wherecondition argument is 32,768 characters. This may seem like a lot, but I have actually run into this limit when using part number descriptions vs the part number “number”.  So the IN statement many look like this: [State] IN (‘CA’, ‘AZ’, ‘WA’), but the text on the report may look like this: States: California & Arizona & Washington.

Function ListBoxBuilder(lstBoxName As String, strFieldName As String, strReportCriteria As String, intColumnNumberFilter As Integer, intColumnNumberText As Integer) 
 Dim varItem As Variant
 Dim strBuildInStatement As String
 Dim strBuildReportInformation As String

  With Me(lstBoxName)
    If .ItemsSelected.Count = 0 Then
      Exit Function
    Else
        For Each varItem In .ItemsSelected
          strBuildInStatement = strBuildInStatement & "'" & .Column((intColumnNumberFilter), varItem) & "',"
          strBuildReportInformation = strBuildReportInformation & Trim(.Column((intColumnNumberText), varItem)) & " & "
        Next varItem
        strBuildInStatement = "[" & strFieldName & "]" & "IN (" & Mid(strBuildInStatement, 1, Len(strBuildInStatement) - 1) & ")"
              'the above line adds the FieldName, adds the IN statement and removes the last ,
    End If
  End With
  gstrFinalBuild = gstrFinalBuild & strBuildInStatement & "and"    ‘ “and” is added here to prepare for the next list box build
  gstrReportCriteria = gstrReportCriteria & strReportCriteria & Mid(strBuildReportInformation, 1, Len(strBuildReportInformation) - 2) & " and "    ‘”and” is added here to prepare for the next list box build
End Function

To conclude, the final strings may look something like this:
GstrFinalBuild = [PROVINCE_OR_STATE_CODE] IN (‘CA’, ‘AZ’, ‘WA’) and [Salesman] IN (‘78’, ‘42’, ‘89’, ‘23’)
GstrReportCriteria = States: California & Arizona & Washington and District Salesman: Bill Smith & Don Fry & Jill Jones & Brian Adams


Stephen Charles, Fresno, California
sjcharles@mindspring.com

'-------------------------------------------------
'  Demonstration software
'
'  Developed by Stephen J. Charles
'  sjcharles@mindspring.com
'
'  This software can be freely distributed
'  as long as this header block stays in place
'
'  The user accepts all responsiblity for the
'  verification and maintainance of the software
'--------------------------------------------------


# tblReportData

description:
faults cannot be true on the "faults" page.


description:
have to manually put label nujmber on the computer


description:
Rewind numbers are in both meters and feet on the same report


description:
The extruder coil reports are still getting cut off


description:
dosage page shows 0 values going into the machine.


description:
"central station main page" showing setup semi auto


description:
Batch management coil display and maintenance


description:
extruder coils report coil numbers and measurements are getting cut off


description:
calibrating the flow station has been very difficult


description:
production supervisors need the ability to move coils from one work to another


description:
four number can not be entered into a pallet


description:
central station main page showing setup semi auto


description:
incorrect number of batches showing on sign board


# tblWizardDescription

text:
REPORT WIZARD

Following is a brief description of how the report wizard works.

RUNNING A REPORT
1) Select a report from the combo box under the label titled “Select a report”
2) Select some criteria in one or more of the list boxes that are visible. Note: you can select more than one item in a list box or nothing at all, but at least one item must be selected in one of the list boxes.
3) Click on “Print Preview” to preview the report filtered by the criteria you have selected.


You will also note that depending on which report you select, different list boxes will appear.  This functionality is based on the number in the “tag” property of the items in the detail section of the form when compared to the field “BitMaskCrit” in “tblReportWizard”.  Review the procedure “cboReportName_AfterUpdate()” to see how this is done.

Stephen Charles, Fresno, California
sjcharles@mindspring.com

'-------------------------------------------------
'  Demonstration software
'
'  Developed by Stephen J. Charles
'  sjcharles@mindspring.com
'
'  This software can be freely distributed
'  as long as this header block stays in place
'
'  The user accepts all responsiblity for the
'  verification and maintainance of the software
'--------------------------------------------------


