Report with date parameters

[Brent]

I have created reports linked to queries. I will use these same reports on a
daily basis. On Tuesday I need to report Mondays numbers. On Wednesday I
need Monday and Tuesdays numbers on there. And so on until on Monday I need
the whole prior week. How do I do this?

 

[Captain Oh!No!]

In the query design grid, in the criteria line, in the
date field, type one of the following two options
depending on which suits your needs.
Captain Oh!No!

1 [Enter Date]
2 Between [Enter Beginning Date] And [Enter Ending Date]

These will prompt the user to enter the desired date.

 

[DevalilaJohn]

In your event procedure to run ther report add:

Dim offset
Dim stDocName As String

offset = Format(Date, "ddd")
offset = Switch(offset = "sun", 0, _
offset = "mon", 1, _
offset = "tue", 2, _
offset = "wed", 3, _
offset = "thu", 4, _
offset = "fri", 5, _
offset = "sat", 6)

stDocName = [YourReportName

DoCmd.OpenReport stDocName, acPreview, , [YourDateField] > DateAdd("d",
-offset, Date)

Assuming that you keep your data down to a manageable size this will
automate your process.

 

[Brent]

Thanks guys. Got it.

In your event procedure to run ther report add:

Dim offset
Dim stDocName As String

offset = Format(Date, "ddd")
offset = Switch(offset = "sun", 0, _
offset = "mon", 1, _
offset = "tue", 2, _
offset = "wed", 3, _
offset = "thu", 4, _
offset = "fri", 5, _
offset = "sat", 6)

stDocName = [YourReportName

DoCmd.OpenReport stDocName, acPreview, , [YourDateField] > DateAdd("d",
-offset, Date)

Assuming that you keep your data down to a manageable size this will
automate your process.

I have created reports linked to queries. I will use these same reports on a
daily basis. On Tuesday I need to report Mondays numbers. On Wednesday I
need Monday and Tuesdays numbers on there. And so on until on Monday I need
the whole prior week. How do I do this?

 

[Douglas J. Steele]

Something wrong with using the Weekday function, rather than creating your
own? <g>

Weekday(Date, vbSunday) - 1 will return exactly what offset contains when
you're done.

I really question the declaration of offset as a variant, and then sometimes
storing a string in it, and then a number. If you give that code to someone
using a different language, it's going to fail. For instance, a French user
will be getting Dim, Lun, Mar, Mer, Jeu, Ven, Sam rather than Sun, Mon, Tue,
Wed, Thu, Fri, Sat. Therefore, offset is still going to be a string after
the Switch function is applied.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

In your event procedure to run ther report add:

Dim offset
Dim stDocName As String

offset = Format(Date, "ddd")
offset = Switch(offset = "sun", 0, _
offset = "mon", 1, _
offset = "tue", 2, _
offset = "wed", 3, _
offset = "thu", 4, _
offset = "fri", 5, _
offset = "sat", 6)

stDocName = [YourReportName

DoCmd.OpenReport stDocName, acPreview, , [YourDateField] > DateAdd("d",
-offset, Date)

Assuming that you keep your data down to a manageable size this will
automate your process.

I have created reports linked to queries. I will use these same reports on a
daily basis. On Tuesday I need to report Mondays numbers. On Wednesday I
need Monday and Tuesdays numbers on there. And so on until on Monday I need
the whole prior week. How do I do this?

 

[DevalilaJohn]

Point taken, that's why we have these groups so we can all learn a thing or
two.

Something wrong with using the Weekday function, rather than creating your
own? <g>

Weekday(Date, vbSunday) - 1 will return exactly what offset contains when
you're done.

I really question the declaration of offset as a variant, and then sometimes
storing a string in it, and then a number. If you give that code to someone
using a different language, it's going to fail. For instance, a French user
will be getting Dim, Lun, Mar, Mer, Jeu, Ven, Sam rather than Sun, Mon, Tue,
Wed, Thu, Fri, Sat. Therefore, offset is still going to be a string after
the Switch function is applied.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

In your event procedure to run ther report add:

Dim offset
Dim stDocName As String

offset = Format(Date, "ddd")
offset = Switch(offset = "sun", 0, _
offset = "mon", 1, _
offset = "tue", 2, _
offset = "wed", 3, _
offset = "thu", 4, _
offset = "fri", 5, _
offset = "sat", 6)

stDocName = [YourReportName

DoCmd.OpenReport stDocName, acPreview, , [YourDateField] > DateAdd("d",
-offset, Date)

Assuming that you keep your data down to a manageable size this will
automate your process.

I have created reports linked to queries. I will use these same reports on a
daily basis. On Tuesday I need to report Mondays numbers. On Wednesday I
need Monday and Tuesdays numbers on there. And so on until on Monday I need
the whole prior week. How do I do this?