Results 1 to 3 of 3
  1. #1
    Join Date
    May 2013

    Unanswered: Date Calculations


    I manage the production of over 150 reports. Some of which are due
    daily, weekly, bi-weekly & monthly. (some reports have multiple due dates for the current month.) I have the production schedule in a table with multiple DueDate columns: DueDate1, DueDate2, DueDate3, etc. Some reports are due every business day of the week.

    Question #1: Should I display a column for each due date (31 columns accross?) or show a duplicate row for that report for each day the report is due? ( Sales_Report, 5/20/13, Completed
    Sales_Report, 5/28/13, Pending
    Sales _Report, 5/31/13, Pending

    Question#2: is there a formula that can be written to autopopulate the duedate fields for every instance and every month? ie. Report 1 due every Mon, Wed, Fri (5/13/13, 5/15/13, 5/17)

    I am currently putting these dates in manually every month and it would be a pain to continue that given that our reporting production schedule will only increase.

    I appreciate any assistance,

  2. #2
    Join Date
    Jan 2005
    I would store the dates in rows. You could use a function to determine day of week (or you could do so in a query) and run an append query to add the dates you want reports.

    Function WhatDayIsIt(StartDate As Date, EndDate As Date, rptType as String)
    Dim DayDate As Date
    For DayDate = StartDate To EndDate
    Select Case Weekday(DayDate)
    Case 1
    Debug.Print "Sunday"
    Case 2 'Monday
    If Dcount("*","tblReports","[DateDue]=#" & DayDate & "# AND [rptName]='" & rptType & "'") = 0 then
    Docmd runsql "Insert Into tblReports (rptName, DueDate) SELECT DayDate AS DueDate, rptType AS rptName;"
    End If
    'Debug.Print "Monday"
    Case 3
    Debug.Print "Tuesday"
    Case 4
    Debug.Print "Wendsday"
    Case 5
    Debug.Print "Thursday"
    Case 6
    Debug.Print "Friday"
    Case 7
    Debug.Print "Saturday"
    Case Else
    End Select
    Next DayDate
    End Function

  3. #3
    Join Date
    May 2013
    thanks for the code billmeye.
    will try it out and let you know how it worked out.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts