Report based on specific dates

[csarjeant via AccessMonster.com]

I am trying to create a report that prompts the user to enter a "From - To"
date range. The associated form/table has a Date/Time field set to Date( ) so
each record is automatically stored with the date it was created. I have a
query that shows results for all records on a report, now I want to be able
to select only records created between two dates. I'm not sure how/where is
the best place to do this.

Thanks.

 

[Marshall Barton]

I am trying to create a report that prompts the user to enter a "From - To"
date range. The associated form/table has a Date/Time field set to Date( ) so
each record is automatically stored with the date it was created. I have a
query that shows results for all records on a report, now I want to be able
to select only records created between two dates. I'm not sure how/where is
the best place to do this.

The "best" way depends on your general approach to providing
a user interface. You could use Parameter prompts (e.g.
Between [Start Date] and [End Date]) as the criteria for the
date field in the report's record source query. This is
easy to do, but creates a very clumsey UI and does not
generalize to situations where one or both dates are
optional.

I think the most popular is to create a form with rwo
unbound text boxes for users to enter the date range and a
command button to open the report. There are two choices
here, one use criteria like:
Between Forms!theform.txtstart And Forms!theform.txtend
but, again that does not generalize.

IMO, "best" is to use VBA code in the form button's Click
event to generate the OpenReport method's WhereCondition
argument. In the simplest case, the code could look
something like:

Dim strWhere As String
stWhere = "datefield Between " _
& Format(txtstart, "\#yyyy-m-s\#") & " And " _
& Format(txtend, "\#yyyy-m-s\#")
FoCmd.OpenReport "name of report", acViewPreview, , strWhere

 

[csarjeant via AccessMonster.com]

Thanks Marshall, but I am still having difficulty. I'm pretty new at all of
this. Here is what I did:
I created a form with 2 unbound text boxes "FromDate" and "ToDate" along with
a command button to preview the report, as you suggested. However, when I
enter dates and click the button, I get all the records, not just the ones in
the selected date range. This is the code I put in the button's Click event:

Private Sub PreviewReport_Click()
On Error GoTo Err_PreviewReport_Click

Dim strWhere As String
stWhere = "FromDate" & Format(FromDate, "\#yyyy-m-s\#") _
& "ToDate" & Format(ToDate, "\yyyy-m-s\#")
DoCmd.OpenReport "Workorder Details", acViewPreview, , strWhere


Exit_PreviewReport_Click:
Exit Sub

Err_PreviewReport_Click:
MsgBox Err.Description
Resume Exit_PreviewReport_Click

End Sub

I am trying to create a report that prompts the user to enter a "From - To"
date range. The associated form/table has a Date/Time field set to Date( ) so
each record is automatically stored with the date it was created. I have a
query that shows results for all records on a report, now I want to be able
to select only records created between two dates. I'm not sure how/where is
the best place to do this.

The "best" way depends on your general approach to providing
a user interface. You could use Parameter prompts (e.g.
Between [Start Date] and [End Date]) as the criteria for the
date field in the report's record source query. This is
easy to do, but creates a very clumsey UI and does not
generalize to situations where one or both dates are
optional.

I think the most popular is to create a form with rwo
unbound text boxes for users to enter the date range and a
command button to open the report. There are two choices
here, one use criteria like:
Between Forms!theform.txtstart And Forms!theform.txtend
but, again that does not generalize.

IMO, "best" is to use VBA code in the form button's Click
event to generate the OpenReport method's WhereCondition
argument. In the simplest case, the code could look
something like:

Dim strWhere As String
stWhere = "datefield Between " _
& Format(txtstart, "\#yyyy-m-s\#") & " And " _
& Format(txtend, "\#yyyy-m-s\#")
FoCmd.OpenReport "name of report", acViewPreview, , strWhere

 

[John Spencer]

Pardon me for jumping in, but you have a few errors in the string you are
building.

You
== left out the comparison operators
== did not refer to the field in your table/query
== had errors in the format string (used s instead of d and dropped a # mark)

stWhere = "[YourDateField] Between " & Format(Me.FromDate, "\#yyyy-m-d\#") _
& " and " & Format(Me.ToDate, "\#yyyy-m-d\#")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks Marshall, but I am still having difficulty. I'm pretty new at all of
this. Here is what I did:
I created a form with 2 unbound text boxes "FromDate" and "ToDate" along with
a command button to preview the report, as you suggested. However, when I
enter dates and click the button, I get all the records, not just the ones in
the selected date range. This is the code I put in the button's Click event:

Private Sub PreviewReport_Click()
On Error GoTo Err_PreviewReport_Click

Dim strWhere As String
stWhere = "FromDate" & Format(FromDate, "\#yyyy-m-s\#") _
& "ToDate" & Format(ToDate, "\yyyy-m-s\#")
DoCmd.OpenReport "Workorder Details", acViewPreview, , strWhere


Exit_PreviewReport_Click:
Exit Sub

Err_PreviewReport_Click:
MsgBox Err.Description
Resume Exit_PreviewReport_Click

End Sub

I am trying to create a report that prompts the user to enter a "From - To"
date range. The associated form/table has a Date/Time field set to Date( ) so
each record is automatically stored with the date it was created. I have a
query that shows results for all records on a report, now I want to be able
to select only records created between two dates. I'm not sure how/where is
the best place to do this.

The "best" way depends on your general approach to providing
a user interface. You could use Parameter prompts (e.g.
Between [Start Date] and [End Date]) as the criteria for the
date field in the report's record source query. This is
easy to do, but creates a very clumsey UI and does not
generalize to situations where one or both dates are
optional.

I think the most popular is to create a form with rwo
unbound text boxes for users to enter the date range and a
command button to open the report. There are two choices
here, one use criteria like:
Between Forms!theform.txtstart And Forms!theform.txtend
but, again that does not generalize.

IMO, "best" is to use VBA code in the form button's Click
event to generate the OpenReport method's WhereCondition
argument. In the simplest case, the code could look
something like:

Dim strWhere As String
stWhere = "datefield Between " _
& Format(txtstart, "\#yyyy-m-s\#") & " And " _
& Format(txtend, "\#yyyy-m-s\#")
FoCmd.OpenReport "name of report", acViewPreview, , strWhere

 

[csarjeant via AccessMonster.com]

Thanks, John! That fixed it.

Pardon me for jumping in, but you have a few errors in the string you are
building.

You
== left out the comparison operators
== did not refer to the field in your table/query
== had errors in the format string (used s instead of d and dropped a # mark)

stWhere = "[YourDateField] Between " & Format(Me.FromDate, "\#yyyy-m-d\#") _
& " and " & Format(Me.ToDate, "\#yyyy-m-d\#")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks Marshall, but I am still having difficulty. I'm pretty new at all of
this. Here is what I did:

[quoted text clipped - 51 lines]

 

[csarjeant via AccessMonster.com]

Ok, I spoke too soon. I'm back to getting all results again.

Dim strWhere As String
stWhere = "[DateAudited] Between" & Format(Me.FromDate, "\#yyyy-m-d\#") &
"and" & Format(Me.ToDate, "\#yyyy-m-d\#")
DoCmd.OpenReport "Workorder Details", acViewPreview, , strWhere

Thanks, John! That fixed it.

Pardon me for jumping in, but you have a few errors in the string you are
building.

[quoted text clipped - 17 lines]

 

[Duane Hookom]

Now, you forgot 3 spaces in your strWhere. Go back to the recommendations
and find their locations and try again.

--
Duane Hookom
Microsoft Access MVP

Ok, I spoke too soon. I'm back to getting all results again.

Dim strWhere As String
stWhere = "[DateAudited] Between" & Format(Me.FromDate, "\#yyyy-m-d\#") &
"and" & Format(Me.ToDate, "\#yyyy-m-d\#")
DoCmd.OpenReport "Workorder Details", acViewPreview, , strWhere

Thanks, John! That fixed it.

Pardon me for jumping in, but you have a few errors in the string you are
building.

[quoted text clipped - 17 lines]

& Format(txtend, "\#yyyy-m-s\#")
FoCmd.OpenReport "name of report", acViewPreview, , strWhere