Calculated Field in Table - Week Num

[aloftis]

I am trying to create a calculated field in my table, I am trying to use the following code:

DatePart("ww",[Gas]![Date Opened])

I receive the following error message: "The expression DatePart("ww",[Gas]![Date Opened]) cannot be used in a calculated column."

Please let me know what I am doing wrong in Access 2007 as I really need to be able to display the week number in the table, based on the Date Opened field.

 

[pr2-eugin]

Hello aloftis, Welcome to AWF

Thank Access it does not allow you to store this information. You do not store calculations in tables. Normally that is considered a bad design. http://allenbrowne.com/casu-14.html

You can create an Unbound text box and have its Control Source set to the Function.

 

[David R]

Or just put the calculated field into your query and use the query (instead of the table) anywhere you need the field+week.

 

[aloftis]

Thank you pr2-eugin and David.

I believe I will need to utilize pr2-eugin's advice, as I will be creating a form that shows this information and the table will need to updated based on this form. So, my thinking is I will have to link the form directly to the table, rather than a query, right?

It has been awhile since I have done much in Access.

I appreciate your responses.

 

[namliam]

Proper database design rule number 1: Thou shalt not store calculated values, as specially NOT such easy values as weeknumbers....

Database design rule number 2: Thou shalt listen to PR2

Rule number 3: If you dont listen to PR2, remember rule 1

 

[David R]

Thank you pr2-eugin and David.

I believe I will need to utilize pr2-eugin's advice, as I will be creating a form that shows this information and the table will need to updated based on this form. So, my thinking is I will have to link the form directly to the table, rather than a query, right?

It has been awhile since I have done much in Access.

I appreciate your responses.

In almost all cases Access will treat a query based on a single table the same as it would the table itself. You'll be able to update the non-calculated fields, add and delete records, etc.

So I still think you'll save yourself time and energy by putting it in a query. Also if you have to show the same field in a report or another form, you won't have to recreate the wheel. Other than that, yes paul's idea is pretty much identical and will also work.

 

[Cronk]

aloftis

You can update table contents using a form based on a query, provided the query can be changed.