Solved Getting 'last week's' data in Access

Microsoft Office 2010 professional
August 26, 2013 at 14:22:02
Specs: Windows XP
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!


See More: Getting last weeks data in Access

Report •


#1
August 27, 2013 at 11:49:25
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.


Report •

#2
August 27, 2013 at 11:53:26
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.

Report •

#3
August 27, 2013 at 12:32:06
✔ Best Answer
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 6

Law of Logical Argument: Anything is possible if you don't know what you're talking about.


Report •

Related Solutions

#4
August 27, 2013 at 13:08:44
Oh, brilliant! That worked perfectly, thank you so much!

Report •

#5
August 27, 2013 at 13:40:45
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.


Report •

Ask Question