Including dynamic date period in reports - help

[tungkee831]

I've created a report, which I can run any day of the week, and it will return new items from last week. I'd like the report to have the date period for the previous week but this needs to be dynamic. For example, if I run the report today (12/18/07), the report title should have the date period for last week, "12/9/07 - 12/15/07". How can I do this in the report? I tried using the expression builder but I'm lost at coming up with an expression that will return the dates I need.

Any help is greatly appreciated.

-tungkee

 

[CraigDolphin]

In the date field of your source table/query try a criteria like:

Between (DateAdd("d",-Weekday(Date()),Date())-6) and DateAdd("d",-Weekday(Date()),Date())

 

[bacarat]

Hi,
You could make two unbount text boxes in your report, call todays date then subtract the number of days back the report would generate then put that in your report header. Example:

=Now()-8

and

=Now()-4

 

[bacarat]

Or you could use craig's much more efficiant way

 

[tungkee831]

The report already returns the information needed from the previous week. What I'd like is for the title of my report to list the date period for the previous week.

So for example, if I run a report today (12/18/07), the report will return last week's items. The title of the report should read:

"Last Week's Items 12/9/07 - 12/15/07"

The date period there should be dynamic depending on the date which the report is created.

Is there an expression similar to "=Date()" but instead something like "=Last Week Date()"?

Any other ideas?

-tungkee

 

[tungkee831]

Hi,
You could make two unbount text boxes in your report, call todays date then subtract the number of days back the report would generate then put that in your report header. Example:

=Now()-8

and

=Now()-4

That was something I contemplated, but thats almost the same thing as manually typing the date period each time I run the report. I need something more self sufficient. An expression that will just automatically return last week's date period without me doing anything.

 

[CraigDolphin]

In the control source for the textbox on your report put

= "Last week's items " & Format(DateAdd("d",-Weekday(Date()),Date())-6),"Short Date") & " - " & Format(DateAdd("d",-Weekday(Date()),Date()),"ShortDate")

 

[tungkee831]

In the control source for the textbox on your report put

= "Last week's items " & Format(DateAdd("d",-Weekday(Date()),Date())-6),"Short Date") & " - " & Format(DateAdd("d",-Weekday(Date()),Date()),"ShortDate")

I'm assuming that DateAdd is a field that you assume is in my table. In that case I'd use my Date Opened field. But is there a way to do this without referencing any fields?

BTW the expression didn't work for me. I kept getting a, "The expression you entered contains invalid syntax. You may have entered an operand without an operator".

-tungkee

 

[CraigDolphin]

No. Dateadd is a function built into access. Looks like I dropped a closing parenthesis off the end.
Try:

="Last weeks items :" & Format(DateAdd("d",-Weekday(Date()),Date())-6,"Short Date") & " - " & DateAdd("d",-Weekday(Date()),Date())

 

[tungkee831]

Thanks Craigdolphin... That is EXACTLY what I was looking for. BTW, how did you get so good at expressions? I would have been stuck had it not been for you and this forum.

-tungkee

 

[CraigDolphin]

BTW, how did you get so good at expressions?

LOL...if I was GOOD I wouldn't have missed that closing parenthesis!

Mostly I know what I do by searching the forum and reading help files/googling the functions I come across to figure out what they do. And shamelessly copying useful expressions that other folks have come up with.

Glad you got what you needed.

 

[tungkee831]

LOL...if I was GOOD I wouldn't have missed that closing parenthesis!

Mostly I know what I do by searching the forum and reading help files/googling the functions I come across to figure out what they do. And shamelessly copying useful expressions that other folks have come up with.

Glad you got what you needed.

Well then... It looks like I'll have to adopt your ways.

Thanks again,
-tungkee

 

[tungkee831]

Craig,

Any chance you know how to return the name of last month? For example, if I run the report today, I want the title of the report to say something like "Items from the month of November".

I tried the following expression, but that returns the date a month ago:

=DateAdd("m",-Month(Date())-1,Date())

What I need is the month name. I tried the Format function, but I can't get it right.

Any one else out there know how I can get this working?

-tungkee

 

[CraigDolphin]

Have you tried
Format(DateAdd("m",-1,Date()),"mmmm")

 

[tungkee831]

Thanks Craig that works. I tried something similar but not exactly that... Thanks Again!

 

[CraigDolphin]

tungkee, glad to help. For future reference, here's a link provided elsewhere on this forum by RuralGuy that may be of use to you in the future.
http://www.pacificdb.com.au/MVP/Code/Dates.htm