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]

I have created an update query to just re align the week calculations as this seemed the more logical solution

 

[Joe4]

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

A few years back, Microsoft added the ability to add Calculated fields to the Table directly. This is a horrible idea that violates the Rules of Database Normalization and can affect compatibility with other programs. As such, most experienced programmers (including myself) will not touch them. And there also limitations of the types of calculations you can do at the table level see here: Microsoft Access tips: Calculated Fields).

Also, it appears that you cannot use non-static values (like "Now()") in these calculated functions. See: MS Access use DATE() in a calculated field

So I highly recommend re-thinking how you want to do this, and try to do it without Calculated fields on Access tables. If you are linking to Excel files, why not do that part on the Excel side?

 

[gmazza76]

I have created an update query to just re align the week calculations as this seemed the more logical solution as the table I populate is aligned to other business areas and they feed from my table

 

[Joe4]

I have created an update query to just re align the week calculations as this seemed the more logical solution as the table I populate is aligned to other business areas and they feed from my table

That seems like a better approach - doing any calculations in a query.

Note that if this a task you will be repeating time and time again, and you are deleting the old data out of the table you are having your Update Query write to, be sure to Compact & Repair your database regularly to keep database "bloat" down and to keep your database running smoothly (deleting records from tables in Access does not actually purge the records and reduce the size of the database until you run a Compact & Repair).

 

[welshgasman]

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

I only have 2007, so no calculated fields option, but this from CJ_London might well explain the issue?

Calculated Field Question

Good morning all Apologies this may sound an easy question, but I am going round in circles due to "The expression cannot be used in a calculated cell" I have a calculated cell that works as follows, apologies column names arent my choice as it needs to be built so the data output table can be...

www.access-programmers.co.uk

 

[Joe4]

OK, that is the same user posting the same question on another forum!
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.
Be sure to follow & read the link at the end of the rule too!

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.
If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[welshgasman]

OK, that is the same user posting the same question on another forum!
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.
Be sure to follow & read the link at the end of the rule too!

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.
If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Darn, you are right, and I never even noticed?

I never put 2 + 2 together.