Automatic Week number

[James Frater]

I currently have a table 'tbl_date', which has one field, 'event date'.
However on my forms, reports and queries I use the datepart expression to
work out the week number, weekday name, year and various combinations of
those expressions for each 'event date'

However, what would really help me, particularly when it comes to grouping
and sorting data in queries and reports I need the week number to be it's own
field. Is there anyway that I could add a 'week number' field into my table
that would automatically update when I enter a vaule in 'event date'?

Any help, as always, is greatly appreciated.

JAMES

 

[scubadiver]

I can't see that you would have to. It can be done in a query.

WeekNo: format([event date], "ww")

If you really want to you can use the after update event of "event date" to
update the field "weekno"

me.weekno = format([event date], "ww")

 

[Jamie Collins]

I currently have a table 'tbl_date', which has one field, 'event date'.
However on my forms, reports and queries I use the datepart expression to
work out the week number, weekday name, year and various combinations of
those expressions for each 'event date'

However, what would really help me, particularly when it comes to grouping
and sorting data in queries and reports I need the week number to be it's own
field. Is there anyway that I could add a 'week number' field into my table

One common trick is to put such values in a Calendar 'auxiliary' table
to be 'looked up' when required but this is probably only of utility
when *your* definition of week number (or whatever) differs from that
of Microsoft. Storing such values in a regular 'entity' table would
constitute denormalization and should IMO be discouraged.

Jamie.

--