Identify if a week is within the last 5 weeks

G

gmazza76

Well-known Member
Joined
Mar 19, 2011
Messages
767
Office Version
  1. 365
Platform
  1. Windows
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
Code: 
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
 
Sort by date Sort by votes
I have created an update query to just re align the week calculations as this seemed the more logical solution
 
Upvote 0

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
gmazza76 said:
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
Click to expand...
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?
 
Upvote 0
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
 
Upvote 0
gmazza76 said:
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
Click to expand...
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).
 
Upvote 0
gmazza76 said:
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
Click to expand...
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
 
Upvote 0
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.
 
Upvote 0
Joe4 said:
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.
Click to expand...
Darn, you are right, and I never even noticed? :(

I never put 2 + 2 together. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
SQL Join Query
Replies
6
Views
593
welshgasman
welshgasman
J
  • Solved
Converting Month to Weeks from the current date
Replies
8
Views
336
jajatidev
J
RICH937
  • Solved
Power Query: Averaging values when greater than zero
Replies
7
Views
362
RICH937
RICH937
D
  • Question
Enter Calculated field in Pivot Table
Replies
0
Views
979
dougebowl
D
L
  • Solved
Would like to make edits to VBA Calendar Code
Replies
8
Views
507
Legacy 352679
L

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top