I'm having issues with my database. I have a series of reports that are set up and the filters are not working correctly. The entire code forth e report function is this.
during the "Private Function optcombo_Query" it has the filter that I need to work. But i don't think I've included the same type of code during the submit mail function near the top. Any suggestions on how I set this up?
Code:
Option Compare Database
Dim MyFilter As String
Dim MyPath As String
Dim MyFilename As String
Private Sub btnClose_Click()
Close
End Sub
Private Sub btnCustomize_Click()
'MsgBox tradescombo.ListIndex
If cmbMarket.ListIndex = -1 Then
MsgBox "Please select a Market"
Exit Sub
End If
WhereClause = ""
tradeListTxt = ""
WhereClause = "[Markets] =" & cmbMarket.ItemData(cmbMarket.ListIndex)
WhereClause = WhereClause & " AND ([Project City] in (" & city_SelectQuery() & "))" 'city select
If tradelist.ItemsSelected.Count > 0 Then
If Not IsNull(comboMisc.value) Then
WhereClause = WhereClause & " AND (tblProjectInfo.Misctrade = '" & comboMisc.value & "' OR " & trade_SelectQuery() & ")"
Else
WhereClause = WhereClause & " AND (" & trade_SelectQuery() & ")"
End If
Else
If Not IsNull(comboMisc.value) Then
WhereClause = WhereClause & " AND ([Misctrade] = '" & comboMisc.value & "' ) "
End If
End If
WhereClause = WhereClause & " AND (tblProjectInfo.[Project Type] In (" & cmbType_SelectQuery() & " )) " ' types of works
WhereClause = WhereClause & datecheck_Query() 'date select
WhereClause = WhereClause & optcombo_Query("tblProjectInfo") 'status of project
If (optcombo.ListIndex = 4) Then
DoCmd.OpenReport "Report-update", acViewPreview, , WhereClause
Else
DoCmd.OpenReport "Report-Custom", acViewPreview, , WhereClause
End If
End Sub
Sub LoadMarkets()
If cmbMarket.ListIndex = -1 Then
Exit Sub
End If
Select Case cmbType.ListIndex
Case 0 'Any
Report_Tooter = "Commercial/Residential Projects Requesting Contact"
Case 1 ' Commercial
Report_Tooter = "Commercial Projects Requesting Contact"
Case 2 ' Residential
Report_Tooter = "Residential Projects Requesting Contact"
End Select
strSQL = "SELECT tblCounty.CountyName & ' (' & Count(tblCounty.[County ID]) & ')' as ProjectCount, tblCounty.[County ID] " & _
"FROM (tblCounty INNER JOIN tblCities ON tblCounty.[County ID] = tblCities.[County ID]) INNER JOIN tblProjectInfo ON tblCities.[City ID] = tblProjectInfo.City " & _
"WHERE ((tblProjectInfo.Markets) = " & cmbMarket.ItemData(cmbMarket.ListIndex) & ")"
WhereClause = "WHERE Markets = " & cmbMarket.ItemData(cmbMarket.ListIndex)
strSQL = strSQL & " AND (tblProjectInfo.[Project Type] In (" & cmbType_SelectQuery() & " ))" 'project types
strSQL = strSQL & optcombo_Query("tblProjectInfo") 'status of project
strSQL = strSQL & datecheck_Query() 'date select
WhereClause = WhereClause & " AND (tblProjectInfo.[Project Type] In (" & cmbType_SelectQuery() & " ))" 'project types
strSQL = strSQL & " GROUP BY tblCounty.CountyName, tblCounty.[County ID] ORDER BY tblCounty.CountyName"
countylist.RowSource = strSQL
'added on 25-yan-2010
tradelist.RowSource = ""
strSQL = "SELECT tblProjectInfo.Misc & ' (' & count(*) & ')' as misctext, tblProjectInfo.Misc " & _
"FROM tblProjectInfo " & WhereClause & " AND Len(tblProjectInfo.Misc) > 0 "
strSQL = strSQL & " GROUP BY tblProjectInfo.Misc "
comboMisc.RowSource = strSQL
End Sub
Private Sub cmbMarket_Change()
LoadMarkets
Report_Title = cmbMarket.Text & " Report"
End Sub
Private Sub cmbType_Click()
LoadMarkets
End Sub
Private Sub countylist_Click()
county_criteria = ""
If countylist.ItemsSelected.Count = 0 Then
tradelist.RowSource = ""
Exit Sub
End If
txtsql = "SELECT tblTradeList.Trade, count(tblProjectInfo.ProjectKey) as Total, tblTradeList.[Trade Id] " & "FROM tblTradeList INNER JOIN tblProjectInfo ON tblTradeList.[Trade Id] = tblProjectInfo.Trade.Value "
WhereClause = "WHERE tblProjectInfo.[City] in (" & city_SelectQuery() & ")" 'city select
WhereClause = WhereClause & " AND (tblProjectInfo.[Project Type] In (" & cmbType_SelectQuery() & " ))" 'project types
WhereClause = WhereClause & optcombo_Query("tblProjectInfo") 'status of project
WhereClause = WhereClause & datecheck_Query() 'date select
txtsql = txtsql & WhereClause & "GROUP by tblTradeList.Trade,tblTradeList.[Trade ID] ORDER BY tblTradeList.Trade "
county_criteria = countyTxt
'MsgBox txtSQL
tradelist.RowSource = txtsql
'added on 25-yan-2010
strSQL = "SELECT tblProjectInfo.Misc & ' (' & count(*) & ')' as misctext, tblProjectInfo.Misc " & _
"FROM tblProjectInfo " & WhereClause & " AND Len(tblProjectInfo.Misc) > 0 "
strSQL = strSQL & " GROUP BY tblProjectInfo.Misc "
comboMisc.RowSource = strSQL
End Sub
Private Sub datecheck_Click()
LoadMarkets
End Sub
Private Sub Form_Load()
'1 month
date1.value = DateAdd("m", -1, Date)
date2.value = Date
End Sub
Private Sub optcombo_Change()
LoadMarkets
End Sub
Private Function optcombo_Query(table As String) As String
Dim WhereClause As String
WhereClause = " "
Select Case optcombo.ListIndex
Case 0 'all
' do nothing
Case 1 ' only active projects
WhereClause = " AND " & table & ".[newOnHold] = 0 "
Case 2 ' only inactive projects
WhereClause = " AND " & table & ".[newOnHold] = 1 "
Case 3 ' Only Projects on hold
WhereClause = " AND " & table & ".[newOnHold] = 2 "
Case 4 'only active projects and projects on hold
WhereClause = " AND (" & table & ".[newOnHold] = 0 Or " & table & ".[newOnHold] = 2 ) "
End Select
optcombo_Query = WhereClause
End Function
Private Function datecheck_Query() As String
Dim WhereClause, DateFromString, DateToString As String
DateFromString = Replace(Format(CDate(date1.value), "MM dd yyyy"), " ", "/")
DateToString = Replace(Format(CDate(date2.value), "MM dd yyyy"), " ", "/")
WhereClause = " "
If datecheck.value = True Then
WhereClause = " AND ( tblProjectInfo.[Project Date] between #" & DateFromString & "# AND #" & DateToString & "# )"
End If
datecheck_Query = WhereClause
End Function
Private Function city_SelectQuery() As String
Dim ret As String
countyTxt = ""
For Each it In countylist.ItemsSelected
countyTxt = countyTxt & countylist.ItemData(it) & ","
Next
If Len(countyTxt) > 0 Then
countyTxt = Left(countyTxt, Len(countyTxt) - 1)
ret = "SELECT c.[City ID] FROM tblCities as c WHERE c.[County ID] in (" & countyTxt & ")"
Else
ret = "SELECT c.[City ID] FROM tblCities as c "
End If
city_SelectQuery = ret
End Function
Private Function trade_SelectQuery() As String
Dim WhereClause, tradeListTxt As String
tradeListTxt = ""
For Each Item In tradelist.ItemsSelected
tradeListTxt = tradeListTxt & tradelist.ItemData(Item) & ","
Next
If Len(tradeListTxt) > 0 Then
tradeListTxt = Left(tradeListTxt, Len(tradeListTxt) - 1)
WhereClause = "(tblProjectInfo.[ProjectKey] In (SELECT tblProjectInfo.ProjectKey FROM tblProjectInfo WHERE tblProjectInfo.Trade.[Value] in (" & tradeListTxt & ")))"
Else
WhereClause = ""
End If
trade_SelectQuery = WhereClause
End Function
Private Function cmbType_SelectQuery() As String
Dim ret As String
ret = ""
Select Case cmbType.ListIndex
Case 0 'Any
ret = "SELECT tblProjectType.ProjecttypeKey FROM tblProjectType WHERE tblProjectType.Category like '*'"
Case 1 ' Commercial
ret = "SELECT tblProjectType.ProjecttypeKey FROM tblProjectType WHERE tblProjectType.Category like 'COM'"
Case 2 ' Residential
ret = "SELECT tblProjectType.ProjecttypeKey FROM tblProjectType WHERE tblProjectType.Category like 'RES'"
End Select
cmbType_SelectQuery = ret
End Functionduring the "Private Function optcombo_Query" it has the filter that I need to work. But i don't think I've included the same type of code during the submit mail function near the top. Any suggestions on how I set this up?
Last edited:
