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

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"

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

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

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.

any suggestions are helpfull

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?

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

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.

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.

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

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

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.

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

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

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?

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

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?

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"

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

Have you confirmed what the combo is returning? Can you post the db?

It's in Access 2007, I can post it sure, but I think it's rather large,

If it's too big to post, perhaps a sample with just the relevant object(s).

here is the basic database for the form in question,

have fun... :-)

I'm not sure exactly what process you're having trouble with (it often helps with a sample db to give the steps necessary to recreate the problem). When selecting something in the left hand listbox, this SQL gets created:

SELECT tblTradeList.Trade, count(tblProjectInfo.ProjectKey) as Total, tblTradeList.[Trade Id] FROM tblTradeList INNER JOIN tblProjectInfo ON tblTradeList.[Trade Id] = tblProjectInfo.Trade.Value WHERE tblProjectInfo.[City] in (SELECT c.[City ID] FROM tblCities as c WHERE c.[County ID] in (196) ) tblProjectInfo.[newOnHold] = 0 GROUP by tblTradeList.Trade,tblTradeList.[Trade ID] ORDER BY tblTradeList.Trade

As noted earlier, there's no AND or OR between the subquery and tblProjectInfo.[newOnHold] = 0

Sorry, i'm still new with all this, The steps to create the issue, Open the form, select a market, and select a county, The trade field should populate. when you select from Active to all, you will see the trade field fill up with every proejct weather it's OK, Inactive, or on hold, Any other selections shows a blank screen. The same goes for printing out the projects when you hit "Customize"

It was selecting a county that I saw that, so that invalid SQL is part of your problem. You need to add AND or OR as appropriate. Also, newOnHold doesn't seem to be a valid field, as once I solve the other problem, I get a parameter prompt on that.

hold on, I used an old backup of the tables, Let me correct the "new on hold" field and email it to you

Before you bother attaching again, try to fix the other problem. That may fix everything.

Here is the corrected version. I'm still not understanding where I add the And or OR function. I've gone over every line and I just am not seeing it.

I'm sorry for sounding stupid.. LOL

You have

Variable = "Apples"
Variable = Variable & "Oranges"

which results in

"ApplesOranges"

Wouldn't you add AND and some spaces either to the end of the first or beginning of the second to end up with

"Apples AND Oranges"

I see what your saying, Unfortunatly, I didnt write the code in the first place, so alot of it is above my head, I'm just trying to fix it with the update change. The original coder I'm not able to get in touch with, if you can give me an exact line so I can see where in the code your see'ing, it might make more sense to me.

sorry to be a pest..

:-(

Ok... I finaly get what you were saying.. the original code was
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 needed to put AND here....

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


Thanks for all the help!