Multiple Selections (1 Viewer)

[Officeboy]

Hello,

I'm making a report based on selections my users pick from drop down menus and fields. I recently changed the format of one of 2 of the fields (they were check boxes) into a list box. I'm having some issues getting the list box to function and filter what I need. it's a combo box.

I just need to know the proper code to make this case function work.. I know i'm doing something wrong.. can you help?

Select Case optcombo.ListIndex
Case 0 'all
' do nothing
Case 1 ' Only active projects
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 0"
Case 2 ' Only inactive projects
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 1"
Case 3 ' Only Projects on hold
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 2"
Case 4 'Only active projects and projects on hold
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 0"
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 2"
End Select

 

[pbaldy]

It would probably help to see the rest of the code, for context. I suspect you want:

Case 4 'Only active projects and projects on hold
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 0 OR tblProjectInfo.[newOnHold] = 2"

 

[Officeboy]

Dim county_criteria As String
Dim MyFilter As String
Dim MyPath As String
Dim MyFilename As String


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)


If Len(county_criteria) > 0 Then
whereclause = whereclause & " AND ([Project City] in ( " & _
"SELECT c.[City ID] FROM tblCities as c " & _
"WHERE c.[County ID] in (" & county_criteria & ") ) ) "
End If

If tradelist.ItemsSelected.Count > 0 Then
'MsgBox "You must select a least one item of the list"
'Exit Sub
tradelisttxt = ""
For Each it In tradelist.ItemsSelected
tradelisttxt = tradelisttxt & tradelist.ItemData(it) & ","
Next
tradelisttxt = Left(tradelisttxt, Len(tradelisttxt) - 1)

whereclause = whereclause & " AND (tblProjectInfo.[Trade].value in (" & tradelisttxt & ") ) "
End If

Select Case cmbType.ListIndex
Case 0 'Any
' do nothing
Case 1 ' Commercial
whereclause = whereclause & " AND (tblProjectInfo.[Project Type] In (SELECT tblProjectType.ProjecttypeKey FROM tblProjectType WHERE tblProjectType.Category='COM')) "
Case 2 ' Residential
whereclause = whereclause & " AND (tblProjectInfo.[Project Type] In (SELECT tblProjectType.ProjecttypeKey FROM tblProjectType WHERE tblProjectType.Category='RES')) "
End Select

If datecheck.Value = True Then
whereclause = whereclause & " AND ( tblProjectInfo.[Project Date] between #" & date1.Value & "# AND #" & date2.Value & "# )"
End If


Select Case optcombo.ListIndex
Case 0 'all
' do nothing
Case 1 ' Only active projects
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 0"
Case 2 ' Only inactive projects
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 1"
Case 3 ' Only Projects on hold
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 2"
Case 4 'Only active projects and projects on hold
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 0"
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 2"
End Select

'Select Path Output to:
MyPath = "C:\Tempsend\"
'State the filename.
MyFilename = "(cDate())" & "-" & "Customized Report" & ".pdf"
'MsgBox whereclause
DoCmd.OpenReport "Report-custom", acViewPreview, , whereclause
DoCmd.OutputTo acOutputReport, "Report-custom", acFormatPDF, MyPath & MyFilename, True
DoCmd.Close acReport, "Report-custom"
End Sub

Sub LoadMarkets()
If cmbMarket.ListIndex = -1 Then
Exit Sub
End If

strSQL = "SELECT c.CountyName & ' (' & Count(c.[County ID]) & ')' as ProjectCount, c.[County ID] " & _
"FROM tblCounty AS c INNER JOIN (tblCities AS ct INNER JOIN tblProjectInfo AS p " & _
"ON ct.[City ID] = p.City) ON c.[County ID] = ct.[County ID] " & _
"WHERE p.Markets = " & cmbMarket.ItemData(cmbMarket.ListIndex)


Select Case cmbType.ListIndex
Case 0 'Any
Report_Tooter = "Commercial/Residential Projects Requesting Contact"
Case 1 ' Commercial
strSQL = strSQL & " AND (p.[Project Type] In (SELECT tblProjectType.ProjecttypeKey FROM tblProjectType WHERE tblProjectType.Category='COM')) "
Report_Tooter = "Commercial Projects Requesting Contact"
Case 2 ' Residential
strSQL = strSQL & " AND (p.[Project Type] In (SELECT tblProjectType.ProjecttypeKey FROM tblProjectType WHERE tblProjectType.Category='RES')) "
Report_Tooter = "Residential Projects Requesting Contact"
End Select

strSQL = strSQL & " GROUP BY c.CountyName, c.[County ID] ORDER BY c.CountyName"

countylist.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 "

countyTxt = ""

For Each it In countylist.ItemsSelected
countyTxt = countyTxt & countylist.ItemData(it) & ","
Next
countyTxt = Left(countyTxt, Len(countyTxt) - 1)

whereclause = "WHERE tblProjectInfo.[City] in ( " & _
"SELECT c.[City ID] FROM tblCities as c " & _
"WHERE c.[County ID] in (" & countyTxt & ") ) "

Select Case optcombo.ListIndex
Case 0 'all
' do nothing
Case 1 ' only active projects
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 0"
Case 2 ' only inactive projects
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 1"
Case 3 ' Only Projects on hold
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 2"
Case 4 'only active projects and projects on hold
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 0"
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 2"
End Select

txtsql = txtsql & whereclause & "GROUP by tblTradeList.Trade,tblTradeList.[Trade ID] ORDER BY tblTradeList.Trade "

county_criteria = countyTxt
'MsgBox txtSQL

tradelist.RowSource = txtsql


End Sub

Private Sub datecheck_Click()

End Sub

Private Sub Form_Load()
'1 month
date1.Value = DateAdd("m", -1, Date)
date2.Value = Date

DoCmd.Maximize
End Sub

Private Sub optcombo_Change()
countylist_Click
End Sub

 

[Officeboy]

now, The optcombo.listindex is not filtering out the projects based on "tblProjectInfo.[newOnHold]"

 

[pbaldy]

Did you try my suggestion? I would point out you're going to have a spacing problem, such as:

"...tblProjectInfo.[newOnHold] = 0GROUP by..."

Note the lack of a space before GROUP.

 

[Officeboy]

any suggestions are helpfull

 

[Officeboy]

my issue right now is that the reports are NOT sorting the way they need to when I select the cases, Is the code correct for that function?

 

[Officeboy]

Pbaldy, it's still not sorting correctly, am I using the case functions correctly?

 

[pbaldy]

Sorry, missed that you posted again. The sorting is not being done in the Case function. The way to check the finished SQL is to add:

Debug.Print txtsql

right before the RowSource line. That will print the SQL out to the Immediate window in the VBA editor. You can examine it there, and post it here if you don't see the problem.

That said, I now see you said a report wasn't sorting correctly. Sorting in reports needs to be set up in Sorting and Grouping.

 

[Officeboy]

I spoke wrong, I meant to say the case function were not FILTERING correctly. I already use the sort functions. any enteries that are marked hold, inactive, or OK need to be filtered the correct way.

 

[pbaldy]

Did you use the Debug.Print to debug the string?

 

[Officeboy]

Not yet, was pulled away from my desk, trying it now tho...

 

[pbaldy]

Hmm, I don't see the post here but got an email notification of one. This is the line I meant:

tradelist.RowSource = txtsql

Basically, you want the Debug.Print line somewhere after the SQL string is completed, so you can see the finished product.

 

[Officeboy]

when I customize the report, I get a runtime error - "3075"
" Syntax error (Missing Operator) in query expression "Market = 1tblprojectinfo.[newonhold]=2"

and when I hit Debug it goes and highlights in yellow this line..

DoCmd.OpenReport "Report-custom", acViewPreview, , whereclause

 

[pbaldy]

Does that help you see the problem? It appears two criteria have been jammed together without " AND " or " OR " to separate them.

 

[Officeboy]

I don't see in the code where there is a spaceing issue, there's nothing lit up that shows me mispelling or anything. Usualy when there is a format issue, VB notifies me of a compile error. I copied the code exactly, can you show me where this error is set at?

 

[Officeboy]

now I got a new error

Run time error3075

syntax error(missing operator) in query expression '[Markets] = 7 AND ([Project City]in (SELECT c.[City ID] from tblcities as c where c.[county id] in (711))) tblprojectinfo.[newonhold]=1

 

[Officeboy]

Ok, here's the deal. The original code was this :

Select Case optcombo.ListIndex
Case 0 'all
' do nothing
Case 1 ' only active projects
whereclause = whereclause & " AND tblProjectInfo.[Inactive] = False "
Case 2 ' only inactive projects
whereclause = whereclause & " AND tblProjectInfo.[Inactive] = True "
Case 3
whereclause = whereclause & " AND tblProjectInfo.[on hold] = True "
Case 4 'only active projects and projects on hold
whereclause = whereclause & " AND tblProjectInfo.[Inactive] = False "
End Select


This worked just fine, the fields were yes/no fields. people were clicking both 'hold' and 'inactive' ... I added a new field for inactive that is a combo box listing 3 lines

OK,0
Inactive,1
On Hold, 2

so i changed the code:

Select Case optcombo.ListIndex
Case 0 'all
' do nothing
Case 1 ' only active projects
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 0"
Case 2 ' only inactive projects
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 1"
Case 3 ' Only Projects on hold
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 2"
Case 4 'only active projects and projects on hold
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 0" Or "tblProjectInfo.[newOnHold] = 2"
End Select

I have a feeling that I wrote this part wrong... any suggestions?

 

[pbaldy]

What I posted in post 2, and then yours:

whereclause = whereclause & "tblProjectInfo.[newOnHold] = 0 OR tblProjectInfo.[newOnHold] = 2"
whereclause = whereclause & "tblProjectInfo.[newOnHold] = 0" Or "tblProjectInfo.[newOnHold] = 2"

 

[Officeboy]

the functions are not filtering at all, not just the OR statement. Case 1, 2, and 3 will not filter correctly as well. that's why I think i'm not using the code correctly