By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,374 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Calculating week numbers from dates in other fields

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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",DateSerial(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",DateSerial(Year(Date()),1,1),Date(), 1,1)

--
Dean Covey
www.coveyaccounting.com

MS-Office Certified:
http://www.microsoft.com/learning/mc...st/default.asp

"Simon Pleasants" <pl*****@hotmail.com> wrote in message
news:61********************************@4ax.com...
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

Nov 12 '05 #2

P: n/a
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*****@Telus.Net 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" <pl*****@hotmail.com> wrote in message
news:61********************************@4ax.com...
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

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.