If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Automatic Week number
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 |
#2
|
|||
|
|||
Automatic Week number
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") -- "Loose Change 2nd Edition" has been seen by almost 7 million people on Google video "James Frater" wrote: 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 |
#3
|
|||
|
|||
Automatic Week number
On Sep 20, 10:44 am, James Frater
wrote: 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. -- |
Thread Tools | |
Display Modes | |
|
|