Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-13-2017, 02:10 AM   #1
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 1,361
Thanks: 165
Thanked 196 Times in 182 Posts
Gasman will become famous soon enough
Report on filtered datasheet?

Hi all,
I have a simple form with an unbound combo on it.
In a subform I have a datasheet linked to the combo.
In the datasheet I can filter further for date and let's say telephone number, but any of the fields that are shown.
I wish to put a report button on the form to produce a report, but that only shows the data currently filtered on the datasheet.?

I'm fine with filtering for the combo for the report, but how do I add the rest of the filter criteria?

As an aside, if I filter on a date, that works fine, but if I then filter on a telephone number, I am presented with numbers that do not belong to the records already filtered?, and selecting an incorrect one filters the data to an empty datasheet.
Is there a way around this, as I must admit I thought it worked as per Excel?

TIA

__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 07-13-2017, 03:40 AM   #2
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 1,361
Thanks: 165
Thanked 196 Times in 182 Posts
Gasman will become famous soon enough
Re: Report on filtered datasheet?

Solved !, well partially.
The report part is solved, still working on the multiple filter issue.

Code:
DoCmd.OpenReport "rptBTCalls", acViewPreview, , Me.Controls("sfrmlBTCalls").Form.Filter & "AND Caller='" & Me.txtCallerName & "'"
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 07-13-2017, 04:02 AM   #3
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 1,890
Thanks: 34
Thanked 440 Times in 410 Posts
ridders will become famous soon enough ridders will become famous soon enough
Re: Report on filtered datasheet?

Hi Paul

Looks like you need a space before "AND ...

I find it easier to construct the filter criteria as a string before the open report line.
Also the first part looks wrong to me ... though I think you are saying that bit works ...

Anyway I'd do something like

Code:
Dim strCriteria As String

strCriteria ="BTCallNumber = '" & Me.cboCallNumber & "' AND Caller='" & Me.txtCallerName & "'"

DoCmd.OpenReport "rptBTCalls", acViewPreview, , strCriteria

__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
, Tapatalk


If this answer has helped, please click the Thanks button and/or click the 'reputation' scales symbol on the left and leave a comment. Cheers!
ridders is offline   Reply With Quote
Old 07-13-2017, 04:34 AM   #4
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 1,361
Thanks: 165
Thanked 196 Times in 182 Posts
Gasman will become famous soon enough
Re: Report on filtered datasheet?

Hi Colin,

Yes I can see that there should be a space before the AND, however it works, but I'll correct it in case of further changes.

The filter for the form ends up like
Code:
((([qryBTCalls].[CalledDate] In ("03/07/2017","07/07/2017")))) AND ([qryBTCalls].[CallingNumber]=14953113219)
however I have discovered a problem as the filter is not cleared when the form closes, and is present on reopen, although no filter shows in the datasheet, so will need to work out how/when to clear it.


TIA
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Last edited by Gasman; 07-13-2017 at 06:02 AM.
Gasman is offline   Reply With Quote
Old 07-13-2017, 05:18 AM   #5
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 1,890
Thanks: 34
Thanked 440 Times in 410 Posts
ridders will become famous soon enough ridders will become famous soon enough
Re: Report on filtered datasheet?

If you go with the strCriteria idea, you can set strCriteria ="" when you close the form

Another reason why I use that approach is so I can use a debug.print line whenever I have trouble with SQL for multiple filters
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
, Tapatalk


If this answer has helped, please click the Thanks button and/or click the 'reputation' scales symbol on the left and leave a comment. Cheers!
ridders is offline   Reply With Quote
Old 07-13-2017, 06:00 AM   #6
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 1,361
Thanks: 165
Thanked 196 Times in 182 Posts
Gasman will become famous soon enough
Re: Report on filtered datasheet?

Yes, I've gone that way as well mainly due to the fact there might not be a form filter?
I now clear the subform filter on main form load.

Code:
Private Sub cmdReport_Click()
Dim strFormFilter As String, strReportFilter As String
strReportFilter = "Caller='" & Me.txtCallerName & "'"
strFormFilter = Me.Controls("sfrmBTCalls").Form.Filter
If strFormFilter <> "" Then
    strReportFilter = strFormFilter & " AND " & strReportFilter
End If

DoCmd.OpenReport "rptBTCalls", acViewPreview, , strReportFilter
End Sub

__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Exporting Filtered Datasheet Form LarryB Forms 7 09-17-2015 12:30 AM
Export filtered datasheet recordset to Excel MightyCat Queries 1 10-09-2013 01:54 PM
export a filtered search from a datasheet to an excel janeyg Modules & VBA 5 09-14-2012 12:45 AM
OutputTo using filtered datasheet outputs all records vmon Forms 7 01-15-2008 07:16 AM
Change subtotal when datasheet is filtered vmon Forms 3 03-08-2007 05:47 AM




All times are GMT -8. The time now is 11:53 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World