On Jun 30, 4:02 pm, Salad <o...@vinegar.comwrote:
Quote:
troy_...@comcast.net wrote:
Quote:
I have a form that asks for the month, the year, the number of weeks
in the month and the beginning date for the month.
>
Quote:
I want to be able to:
>
Quote:
Number the weeks (e.g. 1, 2,3, etc.)
Assign a beginning date and an ending date for each week
Loop this for the number of weeks as input on the form
Transfer each week's number with corresponding beginning and ending
dates to a table
>
Quote:
Thanks for the help in advance.
>
>
There's some missing elements in your problem/question. Look at the
Format() function in help. You can set/determine the first day of a
week with one argument.
vbUseSystem 0 Use NLS API setting.
VbSunday 1 Sunday (default)
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday
>
You can also set what indicates the first week of the year.
vbUseSystem 0 Use NLS API setting.
vbFirstJan1 1 Start with week in which January 1 occurs (default).
vbFirstFourDays 2 Start with the first week that has at least four days
in the year.
vbFirstFullWeek 3 Start with the first full week of the year.
>
Here's some examples from the debug window to get the week number.
? format(#1/1/2008#,"ww",vbUseSystemDayOfWeek,vbFirstFourDays)
1
? format(#1/6/2008#,"ww",vbUseSystemDayOfWeek,vbFirstFourDays)
2
? format(#1/1/2008#,"ww",vbUseSystemDayOfWeek,vbFirstFullWeek)
52
? format(#1/6/2008#,"ww",vbUseSystemDayOfWeek,vbFirstFullWeek)
1
? format(#1/1/2008#,"ww",vbUseSystemDayOfWeek,vbFirstJan1)
1
? format(#1/6/2008#,"ww",vbUseSystemDayOfWeek,vbFirstJan1)
2
? format(#1/1/2008#,"ww",vbUseSystemDayOfWeek,vbUseSystem)
1
? format(#1/6/2008#,"ww",vbUseSystemDayOfWeek,vbUseSystem)
2
? format(#12/31/2008#,"ww",vbUseSystemDayOfWeek,vbFirstFourDays)
53
? format(#12/31/2008#,"ww",vbUseSystemDayOfWeek,vbFirstFullWeek)
52
? format(#12/31/2008#,"ww",vbUseSystemDayOfWeek,vbFirstJan1)
53
>
So it's possible to get the week number from a date, the question
is...when does a week start for you?
>
Bosa Nogahttp://www.youtube.com/watch?v=h73T4gLyZNw
My week always starts on a Monday and ends on Sunday. (Sorry, that was
important information.)
Our corporate calendar is a little skewed from the real calendar. My
thought was to circumvent this nuance by setting up a docket of weekly
reports categorized by their respective months. Since some corporate
months can be four or five weeks, I wanted to be able to set up the
month's weeks right at the beginning of the month by selecting a
beginning date and the number of weeks in the month. From there it
should just be a math problem, right?
What I really have is a major problem and since you have been so kind
in your responses, I might as well tell you. Our department (newly
created 8 months ago) keeps a "scorecard" of our activities. This is
done in Excel. I am trying to mimic, in essence, the Excel scorecard
in Access because all the answers are in the database. My problem is
that this scorecard is kept as a monthly summary, with each week's
data manually entered and eventually aggregated in various ways on
other worksheets.
Most of this reporting can be done through queries alone. However, I
have this data I need to capture at the beginning of every week. That
is our planned ships for the week. All of these units have been tagged
with a special code number in the database making it easy to find
them. The problem is is that this information must appear on what is
last week's scorecard, which is tallied at the same time, because this
is our plans for the new week. New week data and last week data has
got me stumped.
So, on Monday morning I need to be able to capture our planned ships
for this new week, while capturing last week's data. If I don't take a
snapshot of these units on Monday, there is no way to determine what
our beginning priority list looked like by the end of the week as
these units' status will change (customer needs to send a PO for
repair, unit has shipped, etc.). This is why I wanted to set up a
table defining my months and weeks- so I could store this data,
effectively taking a snapshot in time of the units' status.
None of this may make much sense but this is a problem I have put a
lot of thought of into, and can't seem to really decide the best way
to pull it off.
Troy
