OK, I have completely confused myself. I have a report generating form (see image below) that I am trying to create multiple reports from, but the syntax and order is making my head spin. Really, It is the multiselect listbox that is throwing me off.
I am using Select Case for the Report Type and Report Criteria as below when populating the listbox.
Code:
Select Case Me.cbxReportType.Value
Case "Defect Reports"
Select Case Me.cbxRptCriteria.Value
Case "Category"
If Me.tbxDate1 = "Optional" Or Me.tbxDate2 = "Optional" Then
MsgBox "Omitting a date range will list all available records.", vbInformation, "ATTENTION"
strSQL = "SELECT DISTINCT Category FROM DefectEvents"
Else
strSQL = "SELECT DISTINCT Category FROM DefectEvents WHERE Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND Date_Time < #" & Format(dt2, "yyyy-mm-dd") & "#;"
End If
Case "Inventory Reports"
**etc.**
And I know that I have to create the SQL statement for the report in a loop for each item selected in the listbox. I am just not sure how. This is what I have so far, but I know it is not right.
Code:
If Me.tbxDate1 = "Optional" Or Me.tbxDate2 = "Optional" Then
MsgBox "Omitting a date range will list all available records.", vbInformation, "ATTENTION"
With Me.lstRptSearchResult For lngRow = 0 To .ListCount - 1
If .Selected(lngRow) Then
strSQL = "SELECT * FROM DefectEvents WHERE Me.cbxRptCriteria = ????? ;"
End If
Next lngRow
End With
Else
With Me.lstRptSearchResult
For lngRow = 0 To .ListCount - 1
If .Selected(lngRow) Then
strSQL = "SELECT * FROM DefectEvents WHERE Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND Date_Time < #" & Format(dt2, "yyyy-mm-dd") & "#;"
End If
Next lngRow
End With
End If
I hope my questions makes some kind of sense. The red question marks are my biggest quest, but structure and syntax too. The second part of the If statement is also in question because I have to search by both the Listbox selection and between the date range.