Need Access 2003 to look at a date and catagorize it as a production week

[amanda27]

I am creating a database where an excel spreadsheet will be imported and then different information will be taken from that spreadsheet to give different reports and graphs. I am having a problems with the graphs but right now my biggest concern is that a date will be inputed and I need Access to read that date and then catagorize it as a production week. First week in March being Production Week 1. I know how to do this writing a lot of little queries where if it falls between this date and that date to make another column say Production Week #. However I am sure there has to be a much simpler way to do this. Does anyone have any suggestions or advice to help me with this?
Thanks

[cyberdwarf]

Hi Amanda

Just a thought...

Might be quicker to work out the date of each Monday for your production weeks, then enter these into a small table, along with their week numbers.

You could then take your input date, calculate the day of the week, deduct as many days as necessary to get to Monday.

Simple matter to look up that date in the table and pull out the week number

HTH

Steve

[amanda27]

Steve
Thanks for trying to help. This is another idea. However this will be an ongoing thing. They will be importing this information weekly and one of the graphs will show information for that production week alone but the others show for production weeks during the past year.
Therefore I will need all this to update each time it has been imported and was hoping I could find a way where it would know without me having to go back in each year and write a new set of queries or make a new table.
I really appreciate your help though. I will keep this in mind.
Thanks again
Amanda

[MMcCarthy]

I am creating a database where an excel spreadsheet will be imported and then different information will be taken from that spreadsheet to give different reports and graphs. I am having a problems with the graphs but right now my biggest concern is that a date will be inputed and I need Access to read that date and then catagorize it as a production week. First week in March being Production Week 1. I know how to do this writing a lot of little queries where if it falls between this date and that date to make another column say Production Week #. However I am sure there has to be a much simpler way to do this. Does anyone have any suggestions or advice to help me with this?
Thanks

You need to use the following function to establish the week number.

DatePart ( interval, date, [firstdayofweek], [firstweekofyear])

e.g.

DatePart ("ww", [DateFieldName], vbSunday, #01/03/2006#)

Just a couple of things regarding the last date in this formula #01/03/2006#.

I use european formula rather than US change it if appropriate.

Also you may need to change slightly depending on how you establish week 1. I think this will return as week 1 the week that has March 1st anywhere in it. If you only want the first full week of March try changing the date to 7th March and this should solve your problem. Test it out.

[MMcCarthy]

BTW you can now use this as part of an query. e.g.

SELECT DatePart ("ww", [DateFieldName], vbSunday, #01/03/2006#) As WeekNo
FROM TableName;

You need to use the following function to establish the week number.

DatePart ( interval, date, [firstdayofweek], [firstweekofyear])

e.g.

DatePart ("ww", [DateFieldName], vbSunday, #01/03/2006#)

Just a couple of things regarding the last date in this formula #01/03/2006#.

I use european formula rather than US change it if appropriate.

Also you may need to change slightly depending on how you establish week 1. I think this will return as week 1 the week that has March 1st anywhere in it. If you only want the first full week of March try changing the date to 7th March and this should solve your problem. Test it out.