Identify if a week is within the last 5 weeks

would it be easier to write this in the query and re make the table at the end?
If so what would be the best formula to use to only identify the last 5 weeks based on the current date/week?

thanks

Why are you trying to write the results back to a table?
Usually, that is not necessary, and can actually be a detriment, as it can take up extra space, cause data integrity issues, and eliminate the dynamic nature of your database.
Typically, you would just use a Query with dynamic criteria to return the last 5 weeks.

It could be as simple as subtracting 35 days (5 weeks times 7 days) form the date in the criteria.
Or maybe it has to be more complicated.

Note that one potential issue I see with the logic path you were going down is that if you go solely by week number, year is not being considered. So it could return data from prior years that have the same week numbers.

welshgasman said:

IIf(Week<=DateAdd("w",-5,Now()),5,0)

Click to expand...

That will only work if all the data is for the same year. If the data covers multiple years, you will also need to work some sort of year check into the criteria.
(Could be a second criteria calculation field to see if Year of Now() matches Year of date you are checking).

Not sure if this is the best possible way to go, but it seems to work for me at the moment

thanks for the information @Joe4 and @welshgasman

Joe4 said:

That will only work if all the data is for the same year. If the data covers multiple years, you will also need to work some sort of year check into the criteria.
(Could be a second criteria calculation field to see if Year of Now() matches Year of date you are checking).

Click to expand...

It is just going to produce a date value, so the year should be taken into account?
I would also use Date() instead of Now() as time element not required?
I am assuming that the badly named field called week, is actually a date?

Good morning @Joe4 and @welshgasman,

I have tried the formula above and still get the error "the expression cannot be used in a calculated field).

The reason I want to add the calculation into the main table is as it will be linked to another file (excel) and the "wk5" column will auto link upto an existing setup that has been running a while. The year indicator wouldnt be an issue in this case as the data resets and runs for 1 year only. ie, Jan the data will be cleared and reset.

I would normally do this the way Joe has suggested and pull the data separately for the given period, but as several reports will be linked to the existing table it becomes a pain as I would need to rebuild everything that is setup which isnt ideal or easy as other people will use the output table.

Is there a way to write the formula as a calculated reference?

thanks

It seems the issue is the DatePart("ww",Now()) that seems to be an issue