How do I Print a Record every 7 days between 2 dates?

**ADezii** · Jul 29 '10, 06:27 PM

Originally posted by chopin

My goal is to print a record every week between 2 dates. For example, let's say I have a beginning date and expiration date respectively of 9/25/08 and 9/25/09

The goal is to track the "active" customer weekly during this range. Then I will do a "count" function to count the number of successions, and this should solve my problem of counting active clients weekly with a push of a button. Doing this manually every month is very difficult!

So with the above example, just doing general math, the interval would roughly be 365/7 = 52 weeks. Thus, because the account was an active client for 52 weeks, I want to "print" these records 52 times. The beginning date will increase every 7 days or every week and will constantly be compared with the expiration date which will remain constant at 9/25/09. Once it hits 9/25/09, the loop will stop.

A sample output should look like:

Interval, Expire, Name of account
9/25/08, 9/25/09, Account Name
10/2/08, 9/25/09, Account Name
10/9/08, 9/25/09, Account Name
10/16/08, 9/25/09, Account Name
.
.
.
9/25/09, 9/25/09, Account Name

Then we go onto the next client, etc.

I also would like to know if there is a way to do this looping through a query rather than DAO.

You could use Nested Loops where the Outer Loop would generate each Client, and the Inner Loop, the appropriate Date Ranges (7 Day Intervals). Inner Loop only is shown below:

Code:

Const conBEGIN_DATE As Date = #9/25/2008#
Const conEnd_DATE As Date = #9/25/2009#
Dim lngDaysDiff As Long
Dim intIntervalCtr As Integer

lngDaysDiff = DateDiff("d", conBEGIN_DATE, conEnd_DATE)

Debug.Print "Interval", "Expire"
Debug.Print "---------------------------"
For intIntervalCtr = 0 To lngDaysDiff Step 7
  Debug.Print DateAdd("d", intIntervalCtr, conBEGIN_DATE), conEnd_DATE
Next

Sample OUTPUT:

Code:

Interval      Expire
---------------------------
9/25/2008     9/25/2009 
10/2/2008     9/25/2009 
10/9/2008     9/25/2009 
10/16/2008    9/25/2009 
10/23/2008    9/25/2009 
10/30/2008    9/25/2009 
11/6/2008     9/25/2009 
11/13/2008    9/25/2009 
11/20/2008    9/25/2009 
11/27/2008    9/25/2009 
12/4/2008     9/25/2009 
12/11/2008    9/25/2009 
12/18/2008    9/25/2009 
12/25/2008    9/25/2009 
1/1/2009      9/25/2009 
1/8/2009      9/25/2009 
1/15/2009     9/25/2009 
1/22/2009     9/25/2009 
1/29/2009     9/25/2009 
2/5/2009      9/25/2009 
2/12/2009     9/25/2009 
2/19/2009     9/25/2009 
2/26/2009     9/25/2009 
3/5/2009      9/25/2009 
3/12/2009     9/25/2009 
3/19/2009     9/25/2009 
3/26/2009     9/25/2009 
4/2/2009      9/25/2009 
4/9/2009      9/25/2009 
4/16/2009     9/25/2009 
4/23/2009     9/25/2009 
4/30/2009     9/25/2009 
5/7/2009      9/25/2009 
5/14/2009     9/25/2009 
5/21/2009     9/25/2009 
5/28/2009     9/25/2009 
6/4/2009      9/25/2009 
6/11/2009     9/25/2009 
6/18/2009     9/25/2009 
6/25/2009     9/25/2009 
7/2/2009      9/25/2009 
7/9/2009      9/25/2009 
7/16/2009     9/25/2009 
7/23/2009     9/25/2009 
7/30/2009     9/25/2009 
8/6/2009      9/25/2009 
8/13/2009     9/25/2009 
8/20/2009     9/25/2009 
8/27/2009     9/25/2009 
9/3/2009      9/25/2009 
9/10/2009     9/25/2009 
9/17/2009     9/25/2009 
9/24/2009     9/25/2009

**chopin** · Jul 30 '10, 01:55 PM

Thank you for your help. The problem is that I need a function that will take non static dates. I resolved this problem and I will share it:

Code:

Option Compare Database
Option Explicit

Public Function ActivesAccounts() As Boolean
On Error Resume Next

Dim db As DAO.Database, rst As DAO.Recordset, sSQL As String
Dim strWeek As Date, strMonth As Integer, strYear As Integer
Dim strAccount As String, strDate As Date, strExpire As Date

Set db = CurrentDb()

sSQL = "SELECT Week, Month, Year, Account, Date, Expire FROM SEOFullfilledWeek"
Set rst = db.OpenRecordset(sSQL, dbOpenSnapshot)

If Not rst.BOF And Not rst.EOF Then

'Dim strWeek2 As Integer, strMonth2 As Integer, strYear2 As Integer
Dim strWeek2 As Date, strMonth2 As Date, strYear2 As Date
  rst.MoveFirst
  
  strWeek = rst!Date - 7
  strAccount = rst!Account
  strDate = rst!Date
  strExpire = rst!Expire
  
  rst.MoveNext
  Do Until rst.EOF
    If strExpire >= strDate Then
    
        strWeek = strWeek + 7
        strWeek2 = strWeek
        strMonth2 = strWeek
        strYear2 = strWeek
        'strWeek2 = Format(strWeek, "ww")
        'strMonth2 = Format(strWeek, "mm")
        'strYear2 = Format(strWeek, "yyyy")
        sSQL = "INSERT INTO actives (WeekNum, MonthNum, YearNum, Account, DateStart, DateEnd) " _
        & "VALUES('" & strWeek2 & "', '" & strMonth2 & "', '" & strYear2 & "', '" & strAccount & "', '" & strDate & "', '" & strExpire & "')"
        strDate = strDate + 7
        db.Execute sSQL
        'Debug.Print strWeek
        Debug.Print sSQL
        'Debug.Print strKeywords
    Else
        rst.MoveNext
          strWeek = rst!Date
          strAccount = rst!Account
          strDate = rst!Date
          strExpire = rst!Expire
    End If
  Loop
  
End If

Set rst = Nothing
Set db = Nothing

End Function

Example of output from debug:

Code:

INSERT INTO actives (WeekNum, MonthNum, YearNum, Account, DateStart, DateEnd) VALUES('8/7/2008', '8/7/2008', '8/7/2008', 'Account Name', '8/7/2008', '3/5/2009')

This code is fully functional, however one problem I am having is converting the first three value dates into a week number, month number and year number accurately. I seem to get it working 95% of the values, but when I start hitting year 2011, the intervals are always off. Here is the code I am using, unsuccessfully:

Code:

strWeek2 = Format(strWeek, "ww")
strMonth2 = Format(strWeek, "mm")
strYear2 = Format(strWeek, "yyyy")

Here is an example of why it is unsuccessful. I may have a date 12/26/2010, and the week number will spit out 2, the month number will spit out 1, and the year number will spit out 2011. These values are so close, but not exact, and I need exact. Any insight would be appreciated. Thank you!

**ADezii** · Jul 30 '10, 02:56 PM

Originally posted by chopin

Thank you for your help. The problem is that I need a function that will take non static dates. I resolved this problem and I will share it:

Code:

Option Compare Database
Option Explicit

Public Function ActivesAccounts() As Boolean
On Error Resume Next

Dim db As DAO.Database, rst As DAO.Recordset, sSQL As String
Dim strWeek As Date, strMonth As Integer, strYear As Integer
Dim strAccount As String, strDate As Date, strExpire As Date

Set db = CurrentDb()

sSQL = "SELECT Week, Month, Year, Account, Date, Expire FROM SEOFullfilledWeek"
Set rst = db.OpenRecordset(sSQL, dbOpenSnapshot)

If Not rst.BOF And Not rst.EOF Then

'Dim strWeek2 As Integer, strMonth2 As Integer, strYear2 As Integer
Dim strWeek2 As Date, strMonth2 As Date, strYear2 As Date
  rst.MoveFirst
  
  strWeek = rst!Date - 7
  strAccount = rst!Account
  strDate = rst!Date
  strExpire = rst!Expire
  
  rst.MoveNext
  Do Until rst.EOF
    If strExpire >= strDate Then
    
        strWeek = strWeek + 7
        strWeek2 = strWeek
        strMonth2 = strWeek
        strYear2 = strWeek
        'strWeek2 = Format(strWeek, "ww")
        'strMonth2 = Format(strWeek, "mm")
        'strYear2 = Format(strWeek, "yyyy")
        sSQL = "INSERT INTO actives (WeekNum, MonthNum, YearNum, Account, DateStart, DateEnd) " _
        & "VALUES('" & strWeek2 & "', '" & strMonth2 & "', '" & strYear2 & "', '" & strAccount & "', '" & strDate & "', '" & strExpire & "')"
        strDate = strDate + 7
        db.Execute sSQL
        'Debug.Print strWeek
        Debug.Print sSQL
        'Debug.Print strKeywords
    Else
        rst.MoveNext
          strWeek = rst!Date
          strAccount = rst!Account
          strDate = rst!Date
          strExpire = rst!Expire
    End If
  Loop
  
End If

Set rst = Nothing
Set db = Nothing

End Function

Example of output from debug:

Code:

INSERT INTO actives (WeekNum, MonthNum, YearNum, Account, DateStart, DateEnd) VALUES('8/7/2008', '8/7/2008', '8/7/2008', 'Account Name', '8/7/2008', '3/5/2009')

This code is fully functional, however one problem I am having is converting the first three value dates into a week number, month number and year number accurately. I seem to get it working 95% of the values, but when I start hitting year 2011, the intervals are always off. Here is the code I am using, unsuccessfully:

Code:

strWeek2 = Format(strWeek, "ww")
strMonth2 = Format(strWeek, "mm")
strYear2 = Format(strWeek, "yyyy")

Here is an example of why it is unsuccessful. I may have a date 12/26/2010, and the week number will spit out 2, the month number will spit out 1, and the year number will spit out 2011. These values are so close, but not exact, and I need exact. Any insight would be appreciated. Thank you!

Try your various Arguments for Year, Week, and Month using the following Syntax:

Code:

Format(#12/26/2010#,"ww",vbSunday,vbFirstJan1)

P.S. - You can also use the DatePart() Function for this.

**chopin** · Jul 30 '10, 03:38 PM

I just tried, and that gave me the same results. When I output the actual date, I seem to get gibberish, I'm not sure if that has anything to do with it.

Here's an output example of output where the dates are printed as dates using the format function:

Code:

INSERT INTO actives (WeekNum, MonthNum, YearNum, Account, DateStart, DateEnd) VALUES('1/9/1900', '1/2/1900', '6/30/1905', 'Account', '3/6/2008', '3/5/2009')
INSERT INTO actives (WeekNum, MonthNum, YearNum, Account, DateStart, DateEnd) VALUES('1/10/1900', '1/2/1900', '6/30/1905', 'Account', '3/13/2008', '3/5/2009')
INSERT INTO actives (WeekNum, MonthNum, YearNum, Account, DateStart, DateEnd) VALUES('1/11/1900', '1/2/1900', '6/30/1905', 'Account', '3/20/2008', '3/5/2009')
INSERT INTO actives (WeekNum, MonthNum, YearNum, Account, DateStart, DateEnd) VALUES('1/12/1900', '1/2/1900', '6/30/1905', 'Account', '3/27/2008', '3/5/2009')
INSERT INTO actives (WeekNum, MonthNum, YearNum, Account, DateStart, DateEnd) VALUES('1/13/1900', '1/3/1900', '6/30/1905', 'Account', '4/3/2008', '3/5/2009')
INSERT INTO actives (WeekNum, MonthNum, YearNum, Account, DateStart, DateEnd) VALUES('1/14/1900', '1/3/1900', '6/30/1905', 'Account', '4/10/2008', '3/5/2009')
INSERT INTO actives (WeekNum, MonthNum, YearNum, Account, DateStart, DateEnd) VALUES('1/15/1900', '1/3/1900', '6/30/1905', 'Account', '4/17/2008', '3/5/2009')
INSERT INTO actives (WeekNum, MonthNum, YearNum, Account, DateStart, DateEnd) VALUES('1/16/1900', '1/3/1900', '6/30/1905', 'Account', '4/24/2008', '3/5/2009')
INSERT INTO actives (WeekNum, MonthNum, YearNum, Account, DateStart, DateEnd) VALUES('1/17/1900', '1/4/1900', '6/30/1905', 'Account', '5/1/2008', '3/5/2009')
INSERT INTO actives (WeekNum, MonthNum, YearNum, Account, DateStart, DateEnd) VALUES('1/18/1900', '1/4/1900', '6/30/1905', 'Account', '5/8/2008', '3/5/2009')
INSERT INTO actives (WeekNum, MonthNum, YearNum, Account, DateStart, DateEnd) VALUES('1/19/1900', '1/4/1900', '6/30/1905', 'Account', '5/15/2008', '3/5/2009')
INSERT INTO actives (WeekNum, MonthNum, YearNum, Account, DateStart, DateEnd) VALUES('1/20/1900', '1/4/1900', '6/30/1905', 'Account', '5/22/2008', '3/5/2009')
INSERT INTO actives (WeekNum, MonthNum, YearNum, Account, DateStart, DateEnd) VALUES('1/21/1900', '1/4/1900', '6/30/1905', 'Account', '5/29/2008', '3/5/2009')
INSERT INTO actives (WeekNum, MonthNum, YearNum, Account, DateStart, DateEnd) VALUES('1/22/1900', '1/5/1900', '6/30/1905', 'Account', '6/5/2008', '3/5/2009')
INSERT INTO actives (WeekNum, MonthNum, YearNum, Account, DateStart, DateEnd) VALUES('1/23/1900', '1/5/1900', '6/30/1905', 'Account', '6/12/2008', '3/5/2009')
INSERT INTO actives (WeekNum, MonthNum, YearNum, Account, DateStart, DateEnd) VALUES('1/24/1900', '1/5/1900', '6/30/1905', 'Account', '6/19/2008', '3/5/2009')
INSERT INTO actives (WeekNum, MonthNum, YearNum, Account, DateStart, DateEnd) VALUES('1/25/1900', '1/5/1900', '6/30/1905', 'Account', '6/26/2008', '3/5/2009')
INSERT INTO actives (WeekNum, MonthNum, YearNum, Account, DateStart, DateEnd) VALUES('1/26/1900', '1/6/1900', '6/30/1905', 'Account', '7/3/2008', '3/5/2009')
INSERT INTO actives (WeekNum, MonthNum, YearNum, Account, DateStart, DateEnd) VALUES('1/27/1900', '1/6/1900', '6/30/1905', 'Account', '7/10/2008', '3/5/2009')
INSERT INTO actives (WeekNum, MonthNum, YearNum, Account, DateStart, DateEnd) VALUES('1/28/1900', '1/6/1900', '6/30/1905', 'Account', '7/17/2008', '3/5/2009')

Here's an output example of output where dates are converted into numbers:

Code:

WeekNum	MonthNum	YearNum	Account	DateStart	DateEnd
2	1	2011	Account	12/26/2010	7/23/2011
3	1	2011	Account	1/2/2011	7/23/2011
4	1	2011	Account	1/9/2011	7/23/2011
5	1	2011	Account	1/16/2011	7/23/2011
6	1	2011	Account	1/23/2011	7/23/2011
7	2	2011	Account	1/30/2011	7/23/2011
8	2	2011	Account	2/6/2011	7/23/2011
9	2	2011	Account	2/13/2011	7/23/2011
10	2	2011	Account	2/20/2011	7/23/2011
11	3	2011	Account	2/27/2011	7/23/2011
12	3	2011	Account	3/6/2011	7/23/2011

You can see how the numbers are slightly off still.

**chopin** · Jul 30 '10, 11:03 PM

Actually I don't need to use this format option within DAO. I just used a query in the query builder, and this has been successful. I still am unsure why this option won't work in DAO, and I hope to sometime figure out why, but the format function within the query section works flawlessly. Perhaps this is just a vba bug.

How do I Print a Record every 7 days between 2 dates?

How do I Print a Record every 7 days between 2 dates?

Comment

Comment

Comment

Comment

Comment