Calculated column in Table

[Indep99]

Hello,
So I have a column that users will enter a target date, then I want to create a column that will remove 7 days based on the target date, I have gone into the table and set up the column (7daysbefore) to "calculated" and the expression builder opens up... So I type [targetdate] - 7 but when I press OK it says: You cannot set "7daysbefore" to the calculated data type. Use add field in themodify fields ribbon to add a field with the calculated data type...

This is really frustrating, can anyone please help me, as I need to create a few columns that are based on the target date that will be 7, 14, 21 days before.

Thanks a lot

 

[Galaxiom]

The principles of Normalization discourage calculated fields in a table. Normally they are calculated as required in queries and the controls on the forms and reports.

Calculated fields in tables were introduced by Microsoft for Access 2010. Unless you absolutely need them they should be avoided as they make it very difficult to convert the database to other database environments.

Such a simple calculation is an unlikely candidate for being "essential".

 

[Indep99]

Thanks for the answer, that's what I had read as well... However, with a query, I don't know how to add another field based on the calculation, I would like a new column created in the report generated from the query to show the new column that will be the target date - 7
I also don't know the exact formula either , I had [tblX.targetdate] - 7

if the formula is correct, I would only need to know how to create a new field in the report based on the query

Thanks

 

[Galaxiom]

In the query designer field box enter:
newfieldname:[tblX.targetdate] - 7

This does work but an alternative is to use the DateAdd function (with a negative increment).

Place a textbox on the report and enter the name you used in the query. This will bind it to the field in the query.

 

[Indep99]

Thank you that works, I will research the DateAdd function

 

[Indep99]

hehe and I just tried DateAdd... even better... thanks a lot!

I guess my last question is how do I reference that newly created column... let's say I've created newfield and I want to use that for another formula

so I have targetdate and newfield
but I want it to only show customers that are 21 days from newfield and less
so I did newfield - date() <= 21 but that didn't work, it said missing expression

I tried to do a make table but then I can't add a third column that will return all the files that fall within 21 days because it says the source and destination tables are the same

 

[Galaxiom]

The query cannot refer to a field created in itself. So either create the field in a subquery or include the full calculation from the existing fields in the expression.

 

[Indep99]

The query cannot refer to a field created in itself. So either create the field in a subquery or include the full calculation from the existing fields in the expression.

Thanks a lot Galaxiom, your answers are greatly appreciated!!

 

[laolas]

let's say I've created newfield and I want to use that for another formula.