# Thread: Generate Weekending Dates Between Two Dates & Grab Value From Field

1. Registered User
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. Registered User
Join Date
Apr 2014
Location
Kentucky
Posts
630
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)
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. Moderator
Join Date
Mar 2009
Posts
5,442
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
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;```