Do Loop Question

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • troy_lee@comcast.net
    #1

    Do Loop Question

    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.

    I want to be able to:

    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

    Thanks for the help in advance.

    Troy Lee
    Tags: None
    • Salad
      #2
      Re: Do Loop Question

      troy_lee@comcas t.net wrote:
      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.
      >
      I want to be able to:
      >
      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
      >
      Thanks for the help in advance.
      >
      Troy Lee
      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",vbUs eSystemDayOfWee k,vbFirstFourDa ys)
      1
      ? format(#1/6/2008#,"ww",vbUs eSystemDayOfWee k,vbFirstFourDa ys)
      2
      ? format(#1/1/2008#,"ww",vbUs eSystemDayOfWee k,vbFirstFullWe ek)
      52
      ? format(#1/6/2008#,"ww",vbUs eSystemDayOfWee k,vbFirstFullWe ek)
      1
      ? format(#1/1/2008#,"ww",vbUs eSystemDayOfWee k,vbFirstJan1)
      1
      ? format(#1/6/2008#,"ww",vbUs eSystemDayOfWee k,vbFirstJan1)
      2
      ? format(#1/1/2008#,"ww",vbUs eSystemDayOfWee k,vbUseSystem)
      1
      ? format(#1/6/2008#,"ww",vbUs eSystemDayOfWee k,vbUseSystem)
      2
      ? format(#12/31/2008#,"ww",vbUs eSystemDayOfWee k,vbFirstFourDa ys)
      53
      ? format(#12/31/2008#,"ww",vbUs eSystemDayOfWee k,vbFirstFullWe ek)
      52
      ? format(#12/31/2008#,"ww",vbUs eSystemDayOfWee k,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 Noga
      http://www.youtube.com/watch?v=h73T4gLyZNw

        Comment

        • troy_lee@comcast.net
          #3
          Re: Do Loop Question

          On Jun 30, 4:02 pm, Salad <o...@vinegar.c omwrote:
          troy_...@comcas t.net wrote:
          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.
          >
          I want to be able to:
          >
          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
          >
          Thanks for the help in advance.
          >
          Troy Lee
          >
          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",vbUs eSystemDayOfWee k,vbFirstFourDa ys)
          1
          ? format(#1/6/2008#,"ww",vbUs eSystemDayOfWee k,vbFirstFourDa ys)
          2
          ? format(#1/1/2008#,"ww",vbUs eSystemDayOfWee k,vbFirstFullWe ek)
          52
          ? format(#1/6/2008#,"ww",vbUs eSystemDayOfWee k,vbFirstFullWe ek)
          1
          ? format(#1/1/2008#,"ww",vbUs eSystemDayOfWee k,vbFirstJan1)
          1
          ? format(#1/6/2008#,"ww",vbUs eSystemDayOfWee k,vbFirstJan1)
          2
          ? format(#1/1/2008#,"ww",vbUs eSystemDayOfWee k,vbUseSystem)
          1
          ? format(#1/6/2008#,"ww",vbUs eSystemDayOfWee k,vbUseSystem)
          2
          ? format(#12/31/2008#,"ww",vbUs eSystemDayOfWee k,vbFirstFourDa ys)
          53
          ? format(#12/31/2008#,"ww",vbUs eSystemDayOfWee k,vbFirstFullWe ek)
          52
          ? format(#12/31/2008#,"ww",vbUs eSystemDayOfWee k,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=h73T4gL yZNw
          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

            Comment

            • CDMAPoster@fortunejames.com
              #4
              Re: Do Loop Question

              On Jun 30, 3:14 pm, troy_...@comcas t.net wrote:
              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.
              >
              I want to be able to:
              >
              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
              >
              Thanks for the help in advance.
              >
              Troy Lee
              Maybe:

              http://groups.google.com/group/microsoft.public.access/msg/c777af0e1fa201c4


              James A. Fortune
              CDMAPoster@Fort uneJames.com

                Comment

                Previous template Next
                Working...