Dear People who know so much more than I,
I'm trying to pass the value of 5 multiselect list boxes, a couple of date fields, a currency field, maybe or maybe not all of them. You know users. I know my strwhere with the dates is out of place.
Code:
Private Sub OpenReportButton_Click()'DoCmd.SetWarnings False
Dim ctlList As Variant
Dim ctlList2 As Variant
Dim ctlList3 As Variant
Dim ctlList4 As Variant
Dim ctlList5 As Variant
Dim Lmnt As Variant
Dim Lmnt2 As Variant
Dim Lmnt3 As Variant
Dim Lmnt4 As Variant
Dim Lmnt5 As Variant
Dim strDelim As String 'Delimiter for this field type.
Dim sSql As String
Dim sSql2 As String
Dim sSql3 As String
Dim sSql4 As String
Dim sSql5 As String
Dim mycriteria As String
Set ctlList = [Forms]![FReportParams]!Salespeople
Set ctlList2 = [Forms]![FReportParams]!Bidders
Set ctlList3 = [Forms]![FReportParams]!ProdGrp
Set ctlList4 = [Forms]![FReportParams]!Projects
Set ctlList5 = [Forms]![FReportParams]!status
strDelim = """"
'Purpose: Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a JET query string.
' 'Date field example. Use the format string to add the # delimiters and get the right international format.
If Not IsNull([Forms]![FReportParams]![BidStartDate_BeginR]) Then
strWhere = strWhere & "([biddate] >= " & Format([Forms]![FReportParams]![BidStartDate_BeginR], conJetDate) & ") AND "
' Debug.Print bidate & " " & BidStartDate_BeginR
End If
If Not IsNull([Forms]![FReportParams]![BidstartDate_EndR]) Then
strWhere = strWhere & "([biddate] <= " & Format([Forms]![FReportParams]![BidstartDate_EndR]) & ") AND "
' Debug.Print bidate & " " & BidstartDate_EndR
End If
sSql = "SELECT * FROM masterquery WHERE topersonid IN ("
For Each Lmnt In ctlList.itemsselected
sSql = sSql & ctlList.ItemData(Lmnt) & ","
Next
'Remove Last comma and single quote and add closing bracket
sSql = Left(sSql, Len(sSql) - 1) & ")"
Debug.Print sSql
sSql3 = "SELECT * FROM masterquery WHERE ProductGrpID IN ("
For Each Lmnt3 In ctlList3.itemsselected
sSql3 = sSql3 & ctlList3.ItemData(Lmnt) & ","
Next
sSql3 = Left(sSql3, Len(sSql3) - 1) & ")" 'Remove Last comma and single quote and add closing bracket
Debug.Print "SSQL3 " & sSql3
sSql4 = "SELECT * FROM masterquery WHERE projectno IN ("
For Each Lmnt In ctlList4.itemsselected
sSql4 = sSql4 & strDelim & ctlList4.ItemData(Lmnt) & strDelim & ","
Next
sSql4 = Left(sSql4, Len(sSql4) - 1) & ")" 'Remove Last comma and single quote and add closing bracket
Debug.Print "SSQL4 " & sSql4
sSql5 = "SELECT * FROM masterquery WHERE bidstatus IN ("
For Each Lmnt In ctlList5.itemsselected
sSql5 = sSql5 & ctlList5.ItemData(Lmnt) & ","
Next
sSql5 = Left(sSql5, Len(sSql5) - 1) & ")" 'Remove Last comma and single quote and add closing bracket
Debug.Print "SSQL5 " & sSql5
sSql2 = "SELECT * FROM masterquery WHERE Bidders IN ("
For Each Lmnt In ctlList2.itemsselected
sSql2 = sSql2 & ctlList2.ItemData(Lmnt) & ","
Next
sSql2 = Left(sSql2, Len(sSql2) - 1) & ")" 'Remove Last comma and single quote and add closing bracket
Debug.Print "SSQL2 " & sSql2
'DoCmd.Openreport Forms![fMainMenu]![FReports].Form![ReportName].Column(3), acViewPreview, mycriteria
DoCmd.SetWarnings False
Forms("freportparams").SetFocus
DoCmd.Minimize
DoCmd.SetWarnings True
End Sub
The immediate window shows me the correct syntax for each variable(?).
Code:
SSQL SELECT * FROM masterquery WHERE topersonid IN (8,28)
SSQL3 SELECT * FROM masterquery WHERE ProductGrpID IN (197)
SSQL4 SELECT * FROM masterquery WHERE projectno IN ("09-29-20-07","09-30-20-010")
SSQL5 SELECT * FROM masterquery WHERE bidstatus IN (1)
SSQL2 SELECT * FROM masterquery WHERE Bidders IN (7,8)
How do I open the report and pass this information into it? As always, THANK YOU in advance!!!!!