Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2014
    Posts
    1

    Unanswered: Generate Weekending Dates Between Two Dates & Grab Value From Field

    Here goes on trying to explain this....

    I have a qry that brings in Area, System, Start Week, Finish Week, Qty, Hours

    The qry Calculates the Number of weeks between the Start Week & Finish Week.

    The qry also averages the Qty for the given duration of the 'project'.

    Example:

    Start Week 8/16/2014
    Finish Week 9/20/2014
    Qty: 50
    Hours: 50

    Calculated:
    Weeks Duration: 7
    Qty per Week: 10



    1. Is there a way to generate all the weekending dates automatically between the two dates:
    8/23/2014
    8/30/2014
    9/6/2014
    9/13/2014
    9/20/2014

    and pull the QTY values for that project in new fields?

    Area System 8/23/2014 8/30/2014 9/6/2014 9/13/2014 9/20/2014
    BOP FWC 10 10 10 10 10

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    Instead of debug.print, you can append the dates to a table or something..
    Code:
    Public Sub dpAllWeekendDates(ByVal pvStartDate, ByVal pvEndDate)
    Dim d As Integer, iDay As Integer
    Dim vDate As Date
    
    For d = 0 To DateDiff("d", pvStartDate, pvEndDate)
       vDate = DateAdd("d", d, pvStartDate)
       iDay = Format(vDate, "w")
       If iDay = vbSaturday Or iDay = vbSunday Then
           Debug.Print vDate, i
            sSql = "INSERT INTO table ([WeekendDate]) values ('" & vDate & "')"
    
       End If
    Next
    End Sub

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Create a calendar table (only once). Here's how:
    Code:
    Public Sub CreateCalendar()
    
        Const c_SQLD As String = "DROP TABLE Tbl_Calendar;"
        Const c_SQLC As String = "CREATE TABLE Tbl_Calendar " & _
                                 "( Cal_Date DATETIME NOT NULL CONSTRAINT PrimaryKey PRIMARY KEY, " & _
                                   "Cal_DateInt INTEGER NOT NULL, " & _
                                   "Cal_Year INTEGER NOT NULL, " & _
                                   "Cal_Month INTEGER NOT NULL, " & _
                                   "Cal_Day INTEGER NOT NULL, " & _
                                   "Cal_YearDay INTEGER NOT NULL, " & _
                                   "Cal_DayNumber INTEGER NOT NULL, " & _
                                   "Cal_WeekNumber INTEGER NOT NULL " & _
                                 ");"
                             
        Const c_SQLI As String = "INSERT INTO Tbl_Calendar ( Cal_Date, Cal_DateInt, Cal_Year, Cal_Month, Cal_Day, " & _
                                                            "Cal_YearDay, Cal_DayNumber, Cal_WeekNumber ) " & _
                                 "VALUES (#@D#, @I, @Y, @M, @A, @R, @N, @W );"
                                 
        Dim varDate As Variant
        Dim strSQL As String
        
        If DCount("*", "MSysObjects", "name = 'Tbl_Calendar'") > 0 Then CodeDb.Execute c_SQLD, dbFailOnError
        CodeDb.Execute c_SQLC, dbFailOnError
        varDate = #1/1/2000#
        Do
            strSQL = Replace(c_SQLI, "@D", varDate)
            strSQL = Replace(strSQL, "@I", CLng(varDate))
            strSQL = Replace(strSQL, "@Y", DatePart("YYYY", varDate))
            strSQL = Replace(strSQL, "@M", DatePart("M", varDate))
            strSQL = Replace(strSQL, "@A", DatePart("D", varDate))
            strSQL = Replace(strSQL, "@R", DatePart("Y", varDate))
            strSQL = Replace(strSQL, "@N", DatePart("W", varDate))
            strSQL = Replace(strSQL, "@W", DatePart("WW", varDate))
            CodeDb.Execute strSQL, dbFailOnError
            varDate = DateAdd("d", 1, varDate)
        Loop Until varDate = #1/1/2051#
        
    End Sub
    2. Supposing that the name of table or query that contains the 2 columns [Start week] and [Finish week] is [Project], use this query:
    Code:
      SELECT Tbl_Calendar.Cal_DayNumber, Tbl_Calendar.Cal_Date
        FROM Project, Tbl_Calendar
      WHERE (Tbl_Calendar.Cal_DayNumber=7) AND 
            (Tbl_Calendar.Cal_Date Between [Project].[Start week] And [Project].[Finish week])
    ORDER BY Tbl_Calendar.Cal_Date;
    Have a nice day!

Tags for this Thread

Posting Permissions

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