Home/ Questions/Q 9710
Next
Answered
computing
  • 0
Asked: 2021-12-02T23:27:53+00:00 In:

Solved Getting ‘Last Week’s’ Data In Access

  • 0

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!

Share

    1 Answer

    1. 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.

      • 0