Issue with Report Filter Property (1 Viewer)

farhanleos · Oct 26, 2017

Gentlemen,

Greetings

I faced a issue with report filtering , my report is based on query data and
I set one filter property which is not impacting the original query
Propery Filter=([ERA Updates]) is not null
I also want to add filter on ERA field which is a date field
without impacting query

is it possible then quide me please I want to filter that field with current year dates

arnelgp · Oct 26, 2017

when you Open the report, set the Where parameter (Filter), eg:

DoCmd.OpenReport ReportName:="yourReport",View:=acViewPreview,WhereCondition:="Not IsNull([ERA Updates]) And [DateField]=#10/26/2017#"

Ranman256 · Oct 26, 2017

have you tried:
docmd.OpenReport "rpt",acViewPreview ,,"[field] is not null"

farhanleos · Oct 26, 2017

Private Sub Report_Load()

DoCmd.OpenReport ReportName:="ERA Updates001",View:=acViewPreview,Where Condition:="Not IsNull([ERA Updates]) And [ERA]=#10/26/2017#"
End Sub

Its not working I got error on Where Conditon?

George Moore · Oct 26, 2017

DoCmd.OpenReport ReportName:="ERA Updates001",View:=acViewPreview,Where Condition:="Not IsNull([ERA Updates]) And [ERA]=#10/26/2017#"
End Sub

Change to WhereCondition (leave out the space)

farhanleos · Oct 26, 2017

the first filter on ERA Updates I did by property as another example in below snapshot

Its not possible to add the date filter there ?

farhanleos · Oct 26, 2017

George Moore said:
DoCmd.OpenReport ReportName:="ERA Updates001",View:=acViewPreview,Where Condition:="Not IsNull([ERA Updates]) And [ERA]=#10/26/2017#"
End Sub

Change to WhereCondition (leave out the space)

I got error you cant switch to different view

arnelgp · Oct 26, 2017

thats a no, no..

dont put it in the Report_Load Event?!

if you have Command Button that Open the
Report, add the code to it's Click Event:

Private Sub yourButton_Click()
DoCmd.OpenReport ReportName:="ERA Updates001",View:=acViewPreview, WhereCondition:="IsNull([ERA Updates])=False And [ERA]=#10/26/2017#"
End Sub

farhanleos · Oct 26, 2017

Now I put in command button click event but

I got empty report , what is the code to filter current year dates
the below I tried but not right I think
[ERA]=DateSerial(Year(date())

arnelgp · Oct 26, 2017

Year([ERA]) =Year(Date())

farhanleos · Oct 26, 2017

Thank you Sir ,
it works perfectly now by below script

DoCmd.OpenReport ReportName:="ERA Updates001", View:=acViewPreview, WhereCondition:="IsNull([ERA Updates])=False And Year([ERA]) =Year(Date()) "

but manually when I open the report not from form , then it gives me complete report not the filtered one

There is any way that it will work by that way also?
because sir I have to merge all reports at the end and if I will add this one as sub report it will not give me required results ?

arnelgp · Oct 26, 2017

i thought you wanted it filtered?
your first post.

anyway on the form that you made, add
a TextBox control. named it YearTextBox.
on it's caption put "Year to Print".
add a Label control at the button.
Set it's Caption to:
"Format: YYYY. Leave blank to print all."

we will filter your report by this Textbox.
If the textbox is left blank then we will not
include any filter. if not blank, then we will filter.

change the Click event of your button:

Private Sub yourButtonName_Click()
Dim lngYear As Long
lngYear = Val("0" & Me.YearTextBox)
If lngYear = 0 Then
' print without filter
DoCmd.OpenReport ReportName:="ERA Update001", View:=acViewPreview

Else
' print with filter
DoCmd.OpenReport ReportName:="ERA Update001", View:=acViewPreview, WhereCondition:="IsNull([ERA Updates])=False And Year([ERA])=Year(Date())"

End If
End Sub

farhanleos · Oct 29, 2017

Sir,

It works but doest meet my concerns
as I told you I have to add this as sub reports with other and merge them in main report .

for that purpose I want the report to populate the filter current year and ISNULL=false
for that I tried to put criteria in query but it filtered the query ..also while putting year criteria its not working

I want simply when I click the report to execute manually not from form
it will show me required result ..
I failed

farhanleos · Oct 29, 2017

this is what I rely on now that I create separate query for both ERA Updates And ERA Updates,
and Design Reports , one issue I faced
I put isNotNull under ERA Updates as Criteria
its works
but I want to filter current year dates also
for that I put Year(Now()) or I tried by Year(Date())

but both criterias on 2 fields wont work together ?
is there right way of sort it out

arnelgp · Oct 29, 2017

You cant filter on report. Its not dynamic as form.

farhanleos · Oct 29, 2017

I did it by this way ..
on query of report
as new last field i put
ERAYear: Year([ERA]) , and under crieteria ..Year(Now())
it filter me the current year
&
For filter notNulldata
simply
I put isNotNull under ERA Updates as Criteria

its working perfectly now

farhanleos · Nov 1, 2017

Thank you sir Arnel

Issue with Report Filter Property (1 Viewer)

farhanleos

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Ranman256

Well-known member

farhanleos

Registered User.

George Moore

Access 2002,2010 & 2016

farhanleos

Registered User.

farhanleos

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

farhanleos

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

farhanleos

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

farhanleos

Registered User.

farhanleos

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

farhanleos

Registered User.

farhanleos

Registered User.

Similar threads

Users who are viewing this thread