By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,508 Members | 1,856 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,508 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
Share this Question
Share on Google+
5 Replies


ADezii
Expert 5K+
P: 8,628
@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
  1. Const conBEGIN_DATE As Date = #9/25/2008#
  2. Const conEnd_DATE As Date = #9/25/2009#
  3. Dim lngDaysDiff As Long
  4. Dim intIntervalCtr As Integer
  5.  
  6. lngDaysDiff = DateDiff("d", conBEGIN_DATE, conEnd_DATE)
  7.  
  8. Debug.Print "Interval", "Expire"
  9. Debug.Print "---------------------------"
  10. For intIntervalCtr = 0 To lngDaysDiff Step 7
  11.   Debug.Print DateAdd("d", intIntervalCtr, conBEGIN_DATE), conEnd_DATE
  12. Next
Sample OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. Interval      Expire
  2. ---------------------------
  3. 9/25/2008     9/25/2009 
  4. 10/2/2008     9/25/2009 
  5. 10/9/2008     9/25/2009 
  6. 10/16/2008    9/25/2009 
  7. 10/23/2008    9/25/2009 
  8. 10/30/2008    9/25/2009 
  9. 11/6/2008     9/25/2009 
  10. 11/13/2008    9/25/2009 
  11. 11/20/2008    9/25/2009 
  12. 11/27/2008    9/25/2009 
  13. 12/4/2008     9/25/2009 
  14. 12/11/2008    9/25/2009 
  15. 12/18/2008    9/25/2009 
  16. 12/25/2008    9/25/2009 
  17. 1/1/2009      9/25/2009 
  18. 1/8/2009      9/25/2009 
  19. 1/15/2009     9/25/2009 
  20. 1/22/2009     9/25/2009 
  21. 1/29/2009     9/25/2009 
  22. 2/5/2009      9/25/2009 
  23. 2/12/2009     9/25/2009 
  24. 2/19/2009     9/25/2009 
  25. 2/26/2009     9/25/2009 
  26. 3/5/2009      9/25/2009 
  27. 3/12/2009     9/25/2009 
  28. 3/19/2009     9/25/2009 
  29. 3/26/2009     9/25/2009 
  30. 4/2/2009      9/25/2009 
  31. 4/9/2009      9/25/2009 
  32. 4/16/2009     9/25/2009 
  33. 4/23/2009     9/25/2009 
  34. 4/30/2009     9/25/2009 
  35. 5/7/2009      9/25/2009 
  36. 5/14/2009     9/25/2009 
  37. 5/21/2009     9/25/2009 
  38. 5/28/2009     9/25/2009 
  39. 6/4/2009      9/25/2009 
  40. 6/11/2009     9/25/2009 
  41. 6/18/2009     9/25/2009 
  42. 6/25/2009     9/25/2009 
  43. 7/2/2009      9/25/2009 
  44. 7/9/2009      9/25/2009 
  45. 7/16/2009     9/25/2009 
  46. 7/23/2009     9/25/2009 
  47. 7/30/2009     9/25/2009 
  48. 8/6/2009      9/25/2009 
  49. 8/13/2009     9/25/2009 
  50. 8/20/2009     9/25/2009 
  51. 8/27/2009     9/25/2009 
  52. 9/3/2009      9/25/2009 
  53. 9/10/2009     9/25/2009 
  54. 9/17/2009     9/25/2009 
  55. 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
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Function ActivesAccounts() As Boolean
  5. On Error Resume Next
  6.  
  7. Dim db As DAO.Database, rst As DAO.Recordset, sSQL As String
  8. Dim strWeek As Date, strMonth As Integer, strYear As Integer
  9. Dim strAccount As String, strDate As Date, strExpire As Date
  10.  
  11. Set db = CurrentDb()
  12.  
  13. sSQL = "SELECT Week, Month, Year, Account, Date, Expire FROM SEOFullfilledWeek"
  14. Set rst = db.OpenRecordset(sSQL, dbOpenSnapshot)
  15.  
  16. If Not rst.BOF And Not rst.EOF Then
  17.  
  18. 'Dim strWeek2 As Integer, strMonth2 As Integer, strYear2 As Integer
  19. Dim strWeek2 As Date, strMonth2 As Date, strYear2 As Date
  20.   rst.MoveFirst
  21.  
  22.   strWeek = rst!Date - 7
  23.   strAccount = rst!Account
  24.   strDate = rst!Date
  25.   strExpire = rst!Expire
  26.  
  27.   rst.MoveNext
  28.   Do Until rst.EOF
  29.     If strExpire >= strDate Then
  30.  
  31.         strWeek = strWeek + 7
  32.         strWeek2 = strWeek
  33.         strMonth2 = strWeek
  34.         strYear2 = strWeek
  35.         'strWeek2 = Format(strWeek, "ww")
  36.         'strMonth2 = Format(strWeek, "mm")
  37.         'strYear2 = Format(strWeek, "yyyy")
  38.         sSQL = "INSERT INTO actives (WeekNum, MonthNum, YearNum, Account, DateStart, DateEnd) " _
  39.         & "VALUES('" & strWeek2 & "', '" & strMonth2 & "', '" & strYear2 & "', '" & strAccount & "', '" & strDate & "', '" & strExpire & "')"
  40.         strDate = strDate + 7
  41.         db.Execute sSQL
  42.         'Debug.Print strWeek
  43.         Debug.Print sSQL
  44.         'Debug.Print strKeywords
  45.     Else
  46.         rst.MoveNext
  47.           strWeek = rst!Date
  48.           strAccount = rst!Account
  49.           strDate = rst!Date
  50.           strExpire = rst!Expire
  51.     End If
  52.   Loop
  53.  
  54. End If
  55.  
  56. Set rst = Nothing
  57. Set db = Nothing
  58.  
  59. End Function
  60.  
Example of output from debug:

Expand|Select|Wrap|Line Numbers
  1. 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
  1. strWeek2 = Format(strWeek, "ww")
  2. strMonth2 = Format(strWeek, "mm")
  3. strYear2 = Format(strWeek, "yyyy")
  4.  
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

ADezii
Expert 5K+
P: 8,628
@chopin
Try your various Arguments for Year, Week, and Month using the following Syntax:
Expand|Select|Wrap|Line Numbers
  1. 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
  1. 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')
  2. 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')
  3. 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')
  4. 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')
  5. 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')
  6. 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')
  7. 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')
  8. 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')
  9. 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')
  10. 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')
  11. 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')
  12. 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')
  13. 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')
  14. 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')
  15. 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')
  16. 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')
  17. 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')
  18. 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')
  19. 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')
  20. 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')
  21.  
Here's an output example of output where dates are converted into numbers:

Expand|Select|Wrap|Line Numbers
  1. WeekNum    MonthNum    YearNum    Account    DateStart    DateEnd
  2. 2    1    2011    Account    12/26/2010    7/23/2011
  3. 3    1    2011    Account    1/2/2011    7/23/2011
  4. 4    1    2011    Account    1/9/2011    7/23/2011
  5. 5    1    2011    Account    1/16/2011    7/23/2011
  6. 6    1    2011    Account    1/23/2011    7/23/2011
  7. 7    2    2011    Account    1/30/2011    7/23/2011
  8. 8    2    2011    Account    2/6/2011    7/23/2011
  9. 9    2    2011    Account    2/13/2011    7/23/2011
  10. 10    2    2011    Account    2/20/2011    7/23/2011
  11. 11    3    2011    Account    2/27/2011    7/23/2011
  12. 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

Post your reply

Sign in to post your reply or Sign up for a free account.