Filtering a report

K

kacey8

Registered User.
Local time
Today, 21:11
Joined
Jun 12, 2014
Messages
180
Hi guys,

I have a load of reports which I currently filter down by date by using the following on the macro to run the report. It runs absolutely perfectly.

Code: 
DoCmd.OpenReport ReportName:="Tenancy Agreed", View:=acViewPreview, _
     WhereCondition:="[Date Created] Between #" & Format(Me.txtFrom, "mm\/dd\/yyyy") & _
     "# And #" & Format(Me.txtTo, "mm\/dd\/yyyy") & "#"

Now I want to adjust it a little. so as well as searching by date I can filter by user. I have a field in the table/report called "user" and I have a list box in the page with the macro with all the users who can be selected listbox is called "users" so how can I get the report to filter by date and the user in the list box?

Thanks once again.
 
Thanks a lot Baldy,

I wish that made sense to me. I am reading through it and get the general jist but am quite lost.
 
What specifically are you not understanding?
 
okay, I think I get it "to a degree" and my questions may be stupid BUT.....

is [lstEmployees] the name of the combo box which my "users" are listed in? on the page for the form?

Also, is "EmpID" ID the field name which relates to that box?

The rest I do get for the more part (or I can see how it works)
 
Yes to both (though the control is a list box, not a combo). Did you see the sample db demonstrating the code?
 
pbaldy said:
Yes to both (though the control is a list box, not a combo). Did you see the sample db demonstrating the code?
Click to expand...

Unfortunately as I was at work I couldn't download the sample (filters on downloading)

will have a look tonight.
 
Code:
Okay, so looking at it I am trying to merge the two codes, the one I use and the one you suggested so I can search by date & by name...

name of List Box[neglist]
name of field [negotiator]


This is how I am thinking of merging the two, does this look correct?

Code: 
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'make sure a selection has been made
If Me.neglist.ItemsSelected.Count = 0 Then
  MsgBox "Please Select at least one Negotiator"
  Exit Sub
End If
'add selected values to string
Set ctl = Me.neglist
For Each varItem In ctl.ItemsSelected
  strWhere = strWhere & ctl.ItemData(varItem) & ","
  'Use this line if your value is text
  'strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"  
Next varItem 
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)
'open the report, restricted to the selected items
DoCmd.OpenReport "RPT_NegOveral", acPreview, , "Negotiator IN(" & strWhere & ")"
     WhereCondition:="[Date Created] Between #" & Format(Me.txtFrom, "mm\/dd\/yyyy") & _
     "# And #" & Format(Me.txtTo, "mm\/dd\/yyyy") & "#"
 
Okay so the above code doesn't work.... I get this....

Code: 
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'make sure a selection has been made
If Me.neglist.ItemsSelected.Count = 0 Then
MsgBox "Please Select at least one Negotiator"
Exit Sub
End If
'add selected values to string
Set ctl = Me.neglist
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ","
'Use this line if your value is text
'strWhere = strWhere & "'" & ctl.ItemData(varItem) & "'," 
Next varItem 
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)
'open the report, restricted to the selected items
DoCmd.OpenReport "RPT_NegOveral", acPreview, , "Negotiator IN(" & strWhere & ")"
WhereCondition:="[Date Created] Between #" & Format(Me.txtFrom, "mm\/dd\/yyyy") & _
"# And #" & Format(Me.txtTo, "mm\/dd\/yyyy") & "#"
and it debugs...

So I removed the "WhereCondition" and bellow, just tried it to see if I could get the name filtering working.

If I select a negotiator which has an entry on the report, the report loads but all the other negotiators are there as well.

If I choose one who IS NOT on the report, rather than returning a report it debugs and highlights the

Code: 
DoCmd.OpenReport "RPT_NegOveral", acPreview, , "Negotiator IN(" & strWhere & ")"

in Yellow.
 
Last edited:
Try

DoCmd.OpenReport "RPT_NegOveral", acPreview, , "Negotiator IN(" & strWhere & ") And [Date Created] Between #" & Format(Me.txtFrom, "mm\/dd\/yyyy") & "# And #" & Format(Me.txtTo, "mm\/dd\/yyyy") & "#"
 
Hi,

I am getting a missing Syntax error (missing operator) in query expression. It's on the last line (open cmd line)

Full Query is bellow

Code: 
Private Sub Command6_Click()
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'make sure a selection has been made
If Me.neglist.ItemsSelected.Count = 0 Then
  MsgBox "Please Select at least one Negotiator"
  Exit Sub
End If
'add selected values to string
Set ctl = Me.neglist
For Each varItem In ctl.ItemsSelected
  strWhere = strWhere & ctl.ItemData(varItem) & ","
  'Use this line if your value is text
  'strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)
'open the report, restricted to the selected items
DoCmd.OpenReport "RPT_NegOveral", acPreview, , "Negotiator IN(" & strWhere & ") And [Date Called] Between #" & Format(Me.txtFrom, "dd\/mm\/yyyy") & "# And #" & Format(Me.txtTo, "dd\/mm\/yyyy") & "#"
End Sub
 
Add this line right before that one and see what you get in the Immediate window:

Debug.Print "Negotiator IN(" & strWhere & ") And [Date Called] Between #" & Format(Me.txtFrom, "dd\/mm\/yyyy") & "# And #" & Format(Me.txtTo, "dd\/mm\/yyyy") & "#"
 
As in like this?

Code: 
strWhere = Left(strWhere, Len(strWhere) - 1)
'open the report, restricted to the selected items
Debug.Print "Negotiator IN(" & strWhere & ") And [Date Called] Between #" & Format(Me.txtFrom, "dd\/mm\/yyyy") & "# And #" & Format(Me.txtTo, "dd\/mm\/yyyy") & "#"
DoCmd.OpenReport "RPT_NegOveral", acPreview, , "Negotiator IN(" & strWhere & ") And [Date Called] Between #" & Format(Me.txtFrom, "dd\/mm\/yyyy") & "# And #" & Format(Me.txtTo, "dd\/mm\/yyyy") & "#"

nevermind my idiocy.

All it gives is this

Code: 
 Negotiator IN(Alex BXXXXXXX) And [Date Called] Between #01/07/2014# And #03/07/2014#
 
Like I said, it's commented out in your code:

'Use this line if your value is text
'strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
 
Okay Mr PBaldy,

Thank you very very much.. it now loads the report...

However (I know, I am so needy)

It doesn't return results on the report, just a blank report with zero values (if I run the report without filtering it, it returns the data)
 
Are you sure there is data that meets the criteria? Perhaps the report is looking for an ID value instead of the name? Can you post the db here?
 
Unfortunately the DB is currently live (the report is an after the fact modification) and as such has confidential information in it that I can not "purge or desensitise" out. Otherwise I think I would have posted it long ago.


It looks like it is the date element which is causing an issue.

I removed the data filter and the report worked perfectly. (returned the results)

the line I removed was this

Code: 
And [Date Called] Between #" & Format(Me.txtFrom, "dd\/mm\/yyyy") & "# And #" & Format(Me.txtTo, "dd\/mm\/yyyy") & "#"
 
You must log in or register to reply here.

Similar threads

A
FILTER PRINT REPORT
Replies
2
Views
665
arnelgp
arnelgp
D
Solved Date is displaying odd behavior
Replies
14
Views
846
Ken Sheridan
Ken Sheridan
J
Date format mm/yyyy
Replies
17
Views
1,424
JPR
J
C
Date Filter not working
Replies
3
Views
985
Gasman
Gasman
T
Report in unbound Form needs dynamic Recordsource and Filter
Replies
15
Views
1,168
sonic8
S

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom