462,892 Members | 710 Online
Need help? Post your question and get tips & solutions from a community of 462,892 IT Pros & Developers. It's quick & easy.

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

 P: 37 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. Jul 29 '10 #1
5 Replies

 Expert 5K+ P: 8,745 @chopin 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: Expand|Select|Wrap|Line Numbers 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: Expand|Select|Wrap|Line Numbers 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  Jul 29 '10 #2

 P: 37 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: Expand|Select|Wrap|Line Numbers 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: Expand|Select|Wrap|Line Numbers 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: Expand|Select|Wrap|Line Numbers 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! Jul 30 '10 #3

 Expert 5K+ P: 8,745 @chopin Try your various Arguments for Year, Week, and Month using the following Syntax: Expand|Select|Wrap|Line Numbers Format(#12/26/2010#,"ww",vbSunday,vbFirstJan1) P.S. - You can also use the DatePart() Function for this. Jul 30 '10 #4

 P: 37 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: Expand|Select|Wrap|Line Numbers 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: Expand|Select|Wrap|Line Numbers 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. Jul 30 '10 #5

 P: 37 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. Jul 30 '10 #6