By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,680 Members | 1,868 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,680 IT Pros & Developers. It's quick & easy.

how to restrict date range that appears on a report?

P: n/a
Could anyone tell me how to restrict the date range for items that
appear on a report? Old items from previous years appear on the
report. I was asked to have only this year's items (and beyond) listed
on the report. How do I do that?
Dec 4 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a

<km*******@hotmail.comschreef in bericht news:0d**********************************@i29g2000 prf.googlegroups.com...
Could anyone tell me how to restrict the date range for items that
appear on a report? Old items from previous years appear on the
report. I was asked to have only this year's items (and beyond) listed
on the report. How do I do that?
Have a look at the recordsource for the report.
This wil probably be a query. You will need to restrict the dates there.
Hint: Look for 'Criteria'

Arno R
Dec 4 '07 #2

P: n/a
On Tue, 4 Dec 2007 06:51:32 -0800 (PST), km*******@hotmail.com wrote:
Could anyone tell me how to restrict the date range for items that
appear on a report? Old items from previous years appear on the
report. I was asked to have only this year's items (and beyond) listed
on the report. How do I do that?
There are several methods.
Either ...
1) Create a query that is used as the record source for the form.
As criteria on the query's Date field, write:
>= DateSerial(Year(Date()),1,1)
The above criteria will return all records for this and 'future'
years.

Or...

2) Open the report directly from a form command button:

DoCmd.OpenReport "ReportName", acViewPreview, , "[DateField] >= #" &
DateSerial(Year(Date),1,1) & "#"

In this case you do not need to create the query.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Dec 4 '07 #3

P: n/a
I tend to open a DateEntry form on openeing the report to enter the date
parameters

Private Sub Report_Open(Cancel As Integer)

Dim StgFilter As String

DoCmd.OpenForm "DateEntry"

CheckDateEntryClosed:
If IsLoaded("DateEntry") Then
Call apWait(5, False) ' Wait 5 seconds
GoTo CheckDateEntryClosed
End If

StgFilter = "MemJoinDate" & DateField
Me.Filter = StgFilter
Me.FilterOn = True

End Sub

The date form has start date and end date, and when you press the OK button,
the following code runs

Function DateRange(Frm As Form) As String

Dim strWhere As String 'Where condition for OpenReport.
Dim EndDate As Date

Const conDateFormat = "\#dd\/mmm\/yyyy\ Hh:Nn:Ss#"

If YrID 0 Then ' Year selected
EndDate = ELookup("YearEnd", "Year", "YearID = " & YrID)
EndDate = DateAdd("d", 1, EndDate) ' Add 1 day
EndDate = DateAdd("s", -1, EndDate) ' Subtract 1 second
1 day
strWhere = " Between " & Format(ELookup("YearStart", "Year", "YearID
= " & YrID), conDateFormat)
strWhere = strWhere & " And " & Format(EndDate, conDateFormat)
GoTo DateRange_Exit
End If

If IsNull(Frm!txtStartDate) Then
If Not IsNull(Frm.txtEndDate) Then 'End date, but no start.
strWhere = DateField & " < " & Format(Frm.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Frm.txtEndDate) Then 'Start date, but no End.
strWhere = DateField & " " & Format(Frm.txtStartDate,
conDateFormat)
Else 'Both start and end dates.
strWhere = DateField & " Between " & Format(Frm.txtStartDate,
conDateFormat) _
& " And " & Format(Frm.txtEndDate, conDateFormat)
End If
End If

DateRange_Exit:
DateRange = strWhere
DateField = strWhere

End Function
<km*******@hotmail.comwrote in message
news:0d**********************************@i29g2000 prf.googlegroups.com...
Could anyone tell me how to restrict the date range for items that
appear on a report? Old items from previous years appear on the
report. I was asked to have only this year's items (and beyond) listed
on the report. How do I do that?

Dec 4 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.