Calculating week numbers from dates in other fields

[Simon Pleasants]

I recently built a database to help keep track of shipments we are
bringing into the country.

Included in the database are a number of fields with dates for
shipment, ETA etc. Each one of these is accompanied by a "week no"
relating to the week in the calendar year (I am sure many people are
familiar with working with week nos and know what I mean).

Currently the users have to refer to wall calendars which have the
week nos listed on them and manually enter it into the appropriate
field.

Is there any way in which I can have this value calculated
automatically by Access so all the user has to do is enter the date in
the date field and the week no field automatically fills itself in?
It would save us a lot of time....

Many thanks
Simon

[Dean Covey]

Re: Calculating week numbers from dates in other fields

I'd use this in an update query. Change the field of [Invoice Date] to
whatever you need but be sure to change it in both parts of this expression.

DateDiff("ww",D ateSerial(Year([Invoice Date]),1,1),[Invoice Date],1,1)

If this is just based of todays date, that is the day you enter it then
set this as a default in your table and change the formula to

DateDiff("ww",D ateSerial(Year( Date()),1,1),Da te(),1,1)

--
Dean Covey

https://www.coveyaccounting.com

MS-Office Certified:

http://www.microsoft.com/learning/mcp/officespecialist/default.asp

"Simon Pleasants" <plesbit@hotmai l.com> wrote in message
news:610080tjhm kdcgsls0us8ta18 oudcd7fri@4ax.c om...[color=blue]
> I recently built a database to help keep track of shipments we are
> bringing into the country.
>
> Included in the database are a number of fields with dates for
> shipment, ETA etc. Each one of these is accompanied by a "week no"
> relating to the week in the calendar year (I am sure many people are
> familiar with working with week nos and know what I mean).
>
> Currently the users have to refer to wall calendars which have the
> week nos listed on them and manually enter it into the appropriate
> field.
>
> Is there any way in which I can have this value calculated
> automatically by Access so all the user has to do is enter the date in
> the date field and the week no field automatically fills itself in?
> It would save us a lot of time....
>
> Many thanks
> Simon[/color]

[Don Leverton]

Re: Calculating week numbers from dates in other fields

Hi Simon,

I was looking at Stehen Lebans "Month Calendar" earlier today and noticed
that it includes weeknumbers.

http://www.lebans.com/monthcalendar.htm

If that doesn't suit you, try
Format([YourDateField], "ww")

--
HTH,
Don
=============== ==============
Use My.Name@Telus.N et for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

=============== =============== ==


"Simon Pleasants" <plesbit@hotmai l.com> wrote in message
news:610080tjhm kdcgsls0us8ta18 oudcd7fri@4ax.c om...[color=blue]
> I recently built a database to help keep track of shipments we are
> bringing into the country.
>
> Included in the database are a number of fields with dates for
> shipment, ETA etc. Each one of these is accompanied by a "week no"
> relating to the week in the calendar year (I am sure many people are
> familiar with working with week nos and know what I mean).
>
> Currently the users have to refer to wall calendars which have the
> week nos listed on them and manually enter it into the appropriate
> field.
>
> Is there any way in which I can have this value calculated
> automatically by Access so all the user has to do is enter the date in
> the date field and the week no field automatically fills itself in?
> It would save us a lot of time....
>
> Many thanks
> Simon[/color]