I need to create a report that will pull back data from 'last week', no matter what day of the week management runs it, and management does not want to run a query first so.... Using Iff(Format(Date(),"ddd")="Mon",Date()-3,Date()-1), I'm able to get 'yesterday's' numbers so there has to be a way to do this with last week or last month or even last year. And remember, management does not want to do anything more than push a button for this.
Help!
Hi GingerLeake! Just to clarify, you want to view the last full business week from Mon-Fri not the last 5 business days starting from today?
For example: if I were to run your report today (8-27-2013) it would pull info from 8-19-2013(Mon.) thru 8-23-2013(Fri)?
Law of Logical Argument: Anything is possible if you don't know what you're talking about.
Correct! And for last month, I'd want everything from July and not from the last 30 days. I've been toying with Month("m"-1), Month(m)-1, etc but to no avail.
Try adding three new fields in your query. The first field would use the date field that already exists in your query to determine the week number. Copy and paste this to the 'Field' line to create a field named 'WeekNumber' (note that you will need to enter a table and field name of your date field)
WeekNumber: Format([TableName]![FieldName],"ww")Copy and paste this into the 'Criteria' line in the same column
Format(Date(),"ww")-1
The second field would need to identify the year for each record and filter down to only this year (because there is a 34th week every year :-) )Copy and paste this to the 'Field' line to create a field named 'Year' (note that you will need to enter a table and field name of your date field)
Year: Year([TableName]![FieldName])Copy and paste this into the 'Criteria' line in the same column
Year(Date())
The Last field will only select working days (Monday-Friday)Copy and paste this to the 'Field' line to create a field named 'Weekday' (note that you will need to enter a table and field name of your date field)
Weekday: Weekday([TableName]![FieldName])Copy and paste this into the 'Criteria' line in the same column
Between 2 And 6Law of Logical Argument: Anything is possible if you don't know what you're talking about.
Oh, brilliant! That worked perfectly, thank you so much!
No problem! I forgot to mention that you can also use this for month, quarter or even year by just slightly modifying what I have above. If you need help with that, just let us know.
Law of Logical Argument: Anything is possible if you don't know what you're talking about.
Ad Choices