Identify if a week is within the last 5 weeks

[gmazza76]

Good afternoon

Ive come up against a query I need to add into a current table so that when data is added week on week I can show a column that will identify the last 5 weeks continually no matter what week of the year it is.

I have tried using the Calculated field in my table calling it week count, but am getting stuck.

I had something in mind of

IIf([Week]<=(DatePart("w",Now()-5),5,0)))

I get an error "cannot be used in a calculated column, is there away round this?

thanks in advance
Gavin

 

[gmazza76]

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

 

[Joe4]

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]

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

 

[Joe4]

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

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

 

[gmazza76]

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

Week Count: IIf([week] Between DatePart("ww",Now(),2)-1 And DatePart("ww",Now(),2)-6,1,0)

 

[gmazza76]

thanks for the information @Joe4 and @welshgasman

 

[welshgasman]

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

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?

 

[gmazza76]

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

 

[gmazza76]

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