Calculated Field Question (1 Viewer)

[gmazza76]

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 linked to other sources that are already built

I can do this in the query, but it would mean re running all the data each week which could be done but as team movements take please it wouldnt align people properly hence why I would like to do this in the main table once the records are appended each week to give a live update

works - IIf([WeekNo]=47,5,0)
tried - IIf([WeekNo]=DateAdd("w",-5,Now()),5,0)
tried - IIf([Week]=DatePart("ww",now()),5,0)

This works but with a static week number. Is there anyway to change this so it can say look at say week minus 1 or another week number.
I have put a couple of examples above but am going round in circles currently.

Thanks in advance
Gavin

 

[CJ_London]

calculated fields have limited functionality - only a few functions are available, date functions are not available.

do your calculations in a query. Users should not be looking at tables anyway

 

[gmazza76]

thanks, I have had to use an update query to amend the the week numbers as the table is linked (currently) to various other areas of the business that use the table as a feeder into their data

 

[CJ_London]

you are building problems into your design.

Use a query based on the table and have that query used by the other areas of the business rather than the table

The longer you leave doing it properly, the harder it gets and a lot more effort will be required to sort it out in the future