Create Calculated Field

[Guest]

I need to create a Week # field based on the adjacent mm/dd/yyyy date field
in my Access table. In Excel 2003 I use the WEEKNUM function to do it. Is
this also possible in Access 2003 using something like the Expression Builder
and, if so, where in table properties can I build such an expression? Is it
better done thru my update query? Thanks for all advice.

 

[Jeff Boyce]

If you are proposing to add a "calculated" field to a table, don't! It is
rarely necessary in a well normalized database (Access is NOT Excel!), and
can cause a lot of extra work and headaches.

Instead, create a query based on the table. Add a new field to the query.
Use the DatePart() function to derive the Week# from the date field. (see
Access HELP on DatePart() for syntax)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

If you want the week number calculated automatically you need to do so in a
query. Create a new query in design view and add your date field to the grid.
In the column next to it, type in the field cell:

myWeek: Format([myDate],"ww")

Where myDate is the name of your date field.

Delordson Kallon
http://www.instantsoftwaretraining.com

 

[Guest]

If you are storing the date you do not need to store the week number in your
table also. Just format the date to show week in your forms and reports.
You can get weeks by using
Format([YourDateFieldName],"ww")