using a filter on a report

[lubbs65]

So I have a report of names on a form and say list of parts they sold.
so it would say something like:

bob bike
joe car
sally ice cream
bob car
bob ice cream
etc...

rather than using filter, or right clicking there name and using "Equals: "name here""

My first idea was if there was a way to perhaps create a combo box on the report.

so you would select the name from a drop down and then every item involved with that person would then show. so, if i selected bob via combo box my report would look like:
bob bike
bob car
bob ice cream

Again though, I am trying to filter a report, not a form.
The form is based off of a query if that will make it any easier. Just because though, I can't simply select a name in the names column and hist "equals" nor can I click a name under the names column and then click "Filter" then check the appropriate names.

If you know of a method that doesn't involve a combo box it should be fine. but I feel like a combo box would be best.

 

[Trevor G]

What version of Access are you using, and which print are you using as Print Preview wont give you a combo.

 

[lubbs65]

im using access 2007. I haven't been using print preview and have not been getting a combo box. I wasn't sure if there would be a way to get one that anyone knew of.

 

[Trevor G]

Ive just done the obvious thing and created a dropdown in a table, and look at it in the Report under several views, but other than design view, it will not show.

 

[boblarson]

You can't put a combo box to filter directly on the report. You need to use a FORM for selecting the filter and then you can re-open the report based on the selections made in the form.

 

[lubbs65]

You can't put a combo box to filter directly on the report. You need to use a FORM for selecting the filter and then you can re-open the report based on the selections made in the form.

can you elaborate? i have never done it this way.
also i have another slight snag.

so say i have a table similar to this:

bob car $100 1/1/2011
bob mustard $300 1/4/2011
Bob light bulb $300 2/4/2011
Sara Car $30 1/2/2011
Sara ketchup $400 1/3/2011
James ketchup $100 3/1/2011


In my report I am trying see a few things:
on I would want a totals column at the bottom. (simple enough). However I also want a totals in the same report (if at all possible) for the same person and have it filtered by date. to in the end show each person, his total ammount, and then a cumulative ammount
so say something like this in the end:

bob $400 (say no date but have it only display information from January using filters)
Sara $430

And then that is it. Is that possible?

 

[boblarson]

can you elaborate? i have never done it this way.

See my sample for a generic report on my website. I use a form for input and then the command to call the report based on what I'm using. Look at the code behind it and see if it gives you some ideas.

also i have another slight snag.

so say i have a table similar to this:

bob car $100 1/1/2011
bob mustard $300 1/4/2011
Bob light bulb $300 2/4/2011
Sara Car $30 1/2/2011
Sara ketchup $400 1/3/2011
James ketchup $100 3/1/2011


In my report I am trying see a few things:
on I would want a totals column at the bottom. (simple enough). However I also want a totals in the same report (if at all possible) for the same person and have it filtered by date. to in the end show each person, his total ammount, and then a cumulative ammount
so say something like this in the end:

bob $400 (say no date but have it only display information from January using filters)
Sara $430

And then that is it. Is that possible?

Yes, it is possible. For the display of the data you would need to use the report's Grouping capability. You can create groups based on the grouping levels you want. And if you want this to filter it would use the same method as the sample to which I have pointed you.

 

[lubbs65]

Bob, this is currently immensely helpful but i am still having some trouble and i thought maybe you would no the reason why:

For my combo boxes if I select: "I want the combo box to look up the values in a table or query" and create my combo box that way, then click the button to open the report the report opens but has no data underneath the fields.

Instead, if i create a combo box through "i will type in the values that i want" it works fine. Maybe you would understand the source for error?

my idea was that perhaps it would be under row source:

SELECT [Contacts].[ID], [Contacts].[Name] FROM Contacts ORDER BY [Name];

Also, would I be able to do the same thing for multiple fields so say tie all of your commands together? if so, how can I combine the following:

Private Sub cmdOpCompanyName_Click()
' this opens the report in preview mode and you select the customer using the single quotes because Me.cboCompanyName
' returns text and text needs to be surrounded by quotes.
    DoCmd.OpenReport "rptOrders", acViewPreview, , "[Customer]='" & Me.cboCompanyName & "'"
End Sub

Private Sub cmdOpDateRange_Click()
' this opens the report in preview mode and you select the order date by using the BETWEEN operator and making sure
' to include the # signs which designate to Access that it is a date.
    DoCmd.OpenReport "rptOrders", acViewPreview, , "[OrderDate] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"
End Sub

Private Sub cmdOpEmployee_Click()
' this opens the report in preview mode and you select the employee ID which is a number so you don't include
' any delimiters for numeric datatypes.
    DoCmd.OpenReport "rptOrders", acViewPreview, , "[EmployeeID]=" & Me.cboEmployee
End Sub

Private Sub cmdOpSingleDate_Click()
' this opens the report in preview mode and you select the single date by using the equals (=) sign and the date
' delimiters (#)
    DoCmd.OpenReport "rptOrders", acViewPreview, , "[OrderDate]=#" & Me.cboOrderDate & "#"
End Sub

to all filter through one command. That is to say that all four fields will filter simultaneously to create an even more specific filter.

 

[boblarson]

Create a function in the form module like this and call it or you can replace your other buttons with just one which calls this:

Function SearchFrm()
  Dim strWhere As String
 
If Len(Me.cboCompanyName & vbNullString) > 0 Then
   strWhere = "[Customer]=" & Chr(34) & Me.cboCompanyName & Chr(34) & " And "
End If
 
If Len(Me.cboFrom & vbNullString) > 0 And Len(Me.cboTo & vbNullString) > 0 Then
    strWhere = strWhere & "[OrderDate] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "# And "
ElseIf Len(Me.cboFrom & vbNullString) > 0 And Len(Me.cboTo & vbNullString) = 0 Then
    strWhere = strWhere & "[OrderDate] >= #" & Me.cboFrom & "# And "
Else If Len(Me.cboFrom & vbNullString) = 0 And Len(Me.cboTo & vbNullString) > 0 Then
    strWhere = strWhere & "[OrderDate] <= #" & Me.cboTo & "# AND "
End If
 
If Len(Me.cboEmployee & vbNullString) > 0 Then
   strWhere = strWhere & "[EmployeeID]=" & Me.cboEmployee & " And "
End If
 
If Len(Me.cboOrderDate & vbNullString) > 0 Then
   strWhere = strWhere & "[OrderDate]=#" & Me.cboOrderDate & "# AND "
End If
 
If strWhere <> vbNullString Then
   strWhere = Left(strWhere, Len(strWhere) - 5)
End If
 
DoCmd.OpenReport "rptOrders", avViewPreview, , strWhere
 
End Function

 

[lubbs65]

^^ wow thanks

do you have any idea why my combo boxes are not working though? Again, when i create a combo box based on a table the filter is not properly working.
However, if i create a custom combo box and manually type in some of the data that I know will be located in the fields and then select one of the fields that was manually entered, this is working fine.