473,396 Members | 1,770 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

home > topics > microsoft access / vba > questions > loop question for dates with numerous times throughout the day

Join Bytes to post your question to a community of 473,396 software developers and data experts.

Loop question for dates with numerous times throughout the day

I gained a ton of knowledge from an older post by ADezii - and for that I thank you !!! However, I need to take it a step further. I need to add dates and numerous times to my table. I can get it to add one time per date - but I need all the times for every date. For example, this is what I get now:
Expand|Select|Wrap|Line Numbers
  1. DoseDate    DoseTime
  2. 9/18/2013    8:00 AM
  3. 9/19/2013    9:00 AM
  4. 9/20/2013    10:00 AM
  5. 9/21/2013    11:00 AM
  6. 9/22/2013    12:00 PM
  7. 9/23/2013    1:00 PM
  8. 9/24/2013    2:00 PM
  9. 9/25/2013    3:00 PM
  10. 9/26/2013    4:00 PM
  11. 9/27/2013    5:00 PM
  12. 9/28/2013    6:00 PM
  13. 9/29/2013    7:00 PM
  14. 9/30/2013    8:00 PM
  15. 10/1/2013    9:00 PM
  16. 10/2/2013    10:00 PM
  17. 9/10/2013    8:00 AM
  18. 9/11/2013    9:00 AM
  19. 9/12/2013    10:00 AM
  20. 9/13/2013    11:00 AM
  21. 9/14/2013    12:00 PM
  22. 9/15/2013    1:00 PM
  23. 9/16/2013    2:00 PM
  24. 9/17/2013    3:00 PM
  25. 9/18/2013    4:00 PM
  26. 9/19/2013    5:00 PM
  27. 9/20/2013    6:00 PM
  28. 9/21/2013    7:00 PM
  29. 9/22/2013    8:00 PM
  30. 9/23/2013    9:00 PM
  31. 9/24/2013    10:00 PM
But I need: 9/17 8:00 a.m. 9/17 9:00 a.m. etc. Here is my (Your) code:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim ResidentMedications As DAO.Recordset
  3. Dim ResidentMedicationDetailTable As DAO.Recordset
  4. Dim intDayCtr As Integer
  5. Dim doses As Integer
  6. Set MyDB = CurrentDb
  7. Set ResidentMedications = MyDB.OpenRecordset("ResidentMedications", dbOpenForwardOnly)
  8. Set ResidentMedicationDetailTable = MyDB.OpenRecordset("ResidentMedicationDetailTable", dbOpenDynaset)
  9.  
  10.  
  11.  
  12. 'Loop thru all the Records
  13. With ResidentMedications
  14.   Do While Not .EOF
  15.     'Break down the Date Range into Linear Dates and ADD to ResidentMedicationDetailTable
  16.     For intDayCtr = 0 To DateDiff("h", ![FirstDose], ![LastDose])
  17.         ResidentMedicationDetailTable.AddNew
  18.         ResidentMedicationDetailTable![DoseDate] = DateAdd("d", intDayCtr, ![Scheduledstartdate])
  19.         ResidentMedicationDetailTable![dosetime] = DateAdd("h", intDayCtr, ![FirstDose])
  20.  ResidentMedicationDetailTable![Medication] = ![Medication]
  21.  ResidentMedicationDetailTable![MedicationID] = ![MedicationID]
  22.         ResidentMedicationDetailTable![ResidentID] = ![ResidentID]
  23.         ResidentMedicationDetailTable![dosetime] = ![FirstDose]
  24.  
  25.         'ResidentMedicationDetailTable![dosetime] = ![Dose2]
  26.         ResidentMedicationDetailTable![dosetime] = DateAdd("h", intDayCtr, ![FirstDose])
  27.          ResidentMedicationDetailTable.Update
  28.     Next
  29.       .MoveNext
  30.   Loop
  31. End With
  32.  
  33. ResidentMedicationDetailTable.Close
  34. ResidentMedications.Close
  35. Set ResidentMedications = Nothing
  36. Set ResidentMedicationDetailTable = Nothing
End Sub

Thank you in advance for any help you can give me!
Sep 18 '13 #1

✓ answered by ADezii

Assuming you have a [DoseDate] Field in a Table named Table1. The following Code will display all Dates along with the 24 Hours of each Date starting at 8:00 A.M.
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rstDates As DAO.Recordset
  3. Const conBASE_TIME As Date = #8:00:00 AM#
  4. Dim bytCtr As Byte
  5.  
  6. Set MyDB = CurrentDb
  7. Set rstDates = MyDB.OpenRecordset("SELECT DISTINCT [DoseDate] from Table1 ORDER BY " & _
  8.                                   "[DoseDate];", dbOpenForwardOnly)
  9.  
  10. Debug.Print "----------------------"
  11.  
  12. With rstDates
  13.   Do While Not .EOF
  14.     For bytCtr = 0 To 23
  15.       Debug.Print ![DoseDate] & " | " & _
  16.                   Format$(DateAdd("h", bytCtr, conBASE_TIME), "hh:mm AM/PM")
  17.     Next
  18.       Debug.Print "----------------------"
  19.         .MoveNext
  20.   Loop
  21. End With
  22.  
  23. Debug.Print "----------------------"
  24.  
  25. rstDates.Close
  26. Set rstDates = Nothing
RESULTS:
Expand|Select|Wrap|Line Numbers
  1. ----------------------
  2. 9/19/2013 | 08:00 AM
  3. 9/19/2013 | 09:00 AM
  4. 9/19/2013 | 10:00 AM
  5. 9/19/2013 | 11:00 AM
  6. 9/19/2013 | 12:00 PM
  7. 9/19/2013 | 01:00 PM
  8. 9/19/2013 | 02:00 PM
  9. 9/19/2013 | 03:00 PM
  10. 9/19/2013 | 04:00 PM
  11. 9/19/2013 | 05:00 PM
  12. 9/19/2013 | 06:00 PM
  13. 9/19/2013 | 07:00 PM
  14. 9/19/2013 | 08:00 PM
  15. 9/19/2013 | 09:00 PM
  16. 9/19/2013 | 10:00 PM
  17. 9/19/2013 | 11:00 PM
  18. 9/19/2013 | 12:00 AM
  19. 9/19/2013 | 01:00 AM
  20. 9/19/2013 | 02:00 AM
  21. 9/19/2013 | 03:00 AM
  22. 9/19/2013 | 04:00 AM
  23. 9/19/2013 | 05:00 AM
  24. 9/19/2013 | 06:00 AM
  25. 9/19/2013 | 07:00 AM
  26. ----------------------
  27. 9/20/2013 | 08:00 AM
  28. 9/20/2013 | 09:00 AM
  29. 9/20/2013 | 10:00 AM
  30. 9/20/2013 | 11:00 AM
  31. 9/20/2013 | 12:00 PM
  32. 9/20/2013 | 01:00 PM
  33. 9/20/2013 | 02:00 PM
  34. 9/20/2013 | 03:00 PM
  35. 9/20/2013 | 04:00 PM
  36. 9/20/2013 | 05:00 PM
  37. 9/20/2013 | 06:00 PM
  38. 9/20/2013 | 07:00 PM
  39. 9/20/2013 | 08:00 PM
  40. 9/20/2013 | 09:00 PM
  41. 9/20/2013 | 10:00 PM
  42. 9/20/2013 | 11:00 PM
  43. 9/20/2013 | 12:00 AM
  44. 9/20/2013 | 01:00 AM
  45. 9/20/2013 | 02:00 AM
  46. 9/20/2013 | 03:00 AM
  47. 9/20/2013 | 04:00 AM
  48. 9/20/2013 | 05:00 AM
  49. 9/20/2013 | 06:00 AM
  50. 9/20/2013 | 07:00 AM
  51. ----------------------

12 1202
I'm sorry. How do I do that?
Sep 18 '13 #2
zmbd
5,501 Expert Mod 4TB
topdesk123
How to add the code tags: > Before Posting (VBA or SQL) Code

If you'll take a few moments to do the setup for options etc... as given in the first section and the links within this article, it will save us some time too (-_-)
Sep 19 '13 #3
zmbd
5,501 Expert Mod 4TB
topdesk123:
The second block of code, is this the actual code that you are using for your project, or is this the code you borrowed as an example?

If this is not the actual code then please post that instead.

Also, which old thread of ADezii were you reading? It might help to have that context.
Sep 19 '13 #4
ADezii
8,834 Expert 8TB
If I interpret your request correctly, you can use Nested Loops to solve the problem. The Outer Loop would consist of Unique Dates Ascending. The Inner Loop would consist of each Hour for the given Unique Date (24 hrs.) Ascending. The Value of Date and Time for each Iteration of the Loop would be appended to a New Table.
Sep 19 '13 #5
Zmbd,
The Code I posted is the actual code I'm using.
Sep 19 '13 #6
Hi ADezii,
I have been searching and reading other forums on nested loops - but I just don't get it. Can you give me a hint?
Sep 19 '13 #7
ADezii
8,834 Expert 8TB
Assuming you have a [DoseDate] Field in a Table named Table1. The following Code will display all Dates along with the 24 Hours of each Date starting at 8:00 A.M.
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rstDates As DAO.Recordset
  3. Const conBASE_TIME As Date = #8:00:00 AM#
  4. Dim bytCtr As Byte
  5.  
  6. Set MyDB = CurrentDb
  7. Set rstDates = MyDB.OpenRecordset("SELECT DISTINCT [DoseDate] from Table1 ORDER BY " & _
  8.                                   "[DoseDate];", dbOpenForwardOnly)
  9.  
  10. Debug.Print "----------------------"
  11.  
  12. With rstDates
  13.   Do While Not .EOF
  14.     For bytCtr = 0 To 23
  15.       Debug.Print ![DoseDate] & " | " & _
  16.                   Format$(DateAdd("h", bytCtr, conBASE_TIME), "hh:mm AM/PM")
  17.     Next
  18.       Debug.Print "----------------------"
  19.         .MoveNext
  20.   Loop
  21. End With
  22.  
  23. Debug.Print "----------------------"
  24.  
  25. rstDates.Close
  26. Set rstDates = Nothing
RESULTS:
Expand|Select|Wrap|Line Numbers
  1. ----------------------
  2. 9/19/2013 | 08:00 AM
  3. 9/19/2013 | 09:00 AM
  4. 9/19/2013 | 10:00 AM
  5. 9/19/2013 | 11:00 AM
  6. 9/19/2013 | 12:00 PM
  7. 9/19/2013 | 01:00 PM
  8. 9/19/2013 | 02:00 PM
  9. 9/19/2013 | 03:00 PM
  10. 9/19/2013 | 04:00 PM
  11. 9/19/2013 | 05:00 PM
  12. 9/19/2013 | 06:00 PM
  13. 9/19/2013 | 07:00 PM
  14. 9/19/2013 | 08:00 PM
  15. 9/19/2013 | 09:00 PM
  16. 9/19/2013 | 10:00 PM
  17. 9/19/2013 | 11:00 PM
  18. 9/19/2013 | 12:00 AM
  19. 9/19/2013 | 01:00 AM
  20. 9/19/2013 | 02:00 AM
  21. 9/19/2013 | 03:00 AM
  22. 9/19/2013 | 04:00 AM
  23. 9/19/2013 | 05:00 AM
  24. 9/19/2013 | 06:00 AM
  25. 9/19/2013 | 07:00 AM
  26. ----------------------
  27. 9/20/2013 | 08:00 AM
  28. 9/20/2013 | 09:00 AM
  29. 9/20/2013 | 10:00 AM
  30. 9/20/2013 | 11:00 AM
  31. 9/20/2013 | 12:00 PM
  32. 9/20/2013 | 01:00 PM
  33. 9/20/2013 | 02:00 PM
  34. 9/20/2013 | 03:00 PM
  35. 9/20/2013 | 04:00 PM
  36. 9/20/2013 | 05:00 PM
  37. 9/20/2013 | 06:00 PM
  38. 9/20/2013 | 07:00 PM
  39. 9/20/2013 | 08:00 PM
  40. 9/20/2013 | 09:00 PM
  41. 9/20/2013 | 10:00 PM
  42. 9/20/2013 | 11:00 PM
  43. 9/20/2013 | 12:00 AM
  44. 9/20/2013 | 01:00 AM
  45. 9/20/2013 | 02:00 AM
  46. 9/20/2013 | 03:00 AM
  47. 9/20/2013 | 04:00 AM
  48. 9/20/2013 | 05:00 AM
  49. 9/20/2013 | 06:00 AM
  50. 9/20/2013 | 07:00 AM
  51. ----------------------
Sep 19 '13 #8
Thank you! Thank you! Thank you! Thank you! ADezii YOU ROCK!
My tables are ResidentMedications and ResidentMedicationsDetailTable, the first table contains the data I am trying to move and expand into the second table based on the fields ScheduledStartDate and ScheduledEndDate and FistDose(Time) and LastDose (time). I am going to try and get your suggestion to work with this. Again, thank you!
Sep 20 '13 #9
ADezii
8,834 Expert 8TB
You are quite welcome, topdesk123.
Sep 20 '13 #10
A step further...I got this code to work nearly perfectly.
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim ResidentMedications As DAO.Recordset
  3. Dim ResidentMedicationDetailTable As DAO.Recordset
  4. Dim intDayCtr As Integer
  5.  
  6. Set MyDB = CurrentDb
  7. Set ResidentMedications = MyDB.OpenRecordset("ResidentMedications", dbOpenForwardOnly)
  8. Set ResidentMedicationDetailTable = MyDB.OpenRecordset("ResidentMedicationDetailTable", dbOpenDynaset)
  9.  
  10. 'Clear all Records in ResidentMedicationDetailTable
  11. 'CurrentDb.Execute "DELETE * FROM ResidentMedicationDetailTable;", dbFailOnError
  12.  
  13.  
  14. With ResidentMedications
  15.  
  16. Do While Not .EOF
  17.  
  18.     'Break down the Date Range into Linear Dates and ADD to ResidentMedicationDetailTable
  19.     For intDayCtr = 0 To DateDiff("d", ![Scheduledstartdate], ![Scheduledenddate]) - 1
  20.       ResidentMedicationDetailTable.AddNew
  21.         ResidentMedicationDetailTable![Dosedate] = DateAdd("d", intDayCtr, ![Scheduledstartdate])
  22.         ResidentMedicationDetailTable![Dosetime] = (DateAdd("h", bytCtr, [FirstDose] + [Interval] / 24))
  23.         ResidentMedicationDetailTable![ResidentID] = ![ResidentID]
  24.           ResidentMedicationDetailTable![Medication] = ![Medication]
  25.             ResidentMedicationDetailTable![MedicationID] = ![MedicationID]
  26.       ResidentMedicationDetailTable.Update
  27.     Next
  28.       .MoveNext
  29.   Loop
  30. End With
  31.  
  32.  
  33.  
  34.  
  35. 'rstDates.Close
  36. ' Set rstDates = Nothing
  37.  
  38. End Sub
However I am wondering how to write the do while statement to say something like "Do while [Dosedate] is between [scheduledstartdate] and [scheduledenddate] and [dosetime] is before [lastdose]. I've tried several variations and continue getting syntax error messages.

thanks in advance.
Sep 20 '13 #11
zmbd
5,501 Expert Mod 4TB
However I am wondering how to write the do while statement to say something like "Do while [Dosedate] is between [scheduledstartdate] and [scheduledenddate] and [dosetime] is before [lastdose]. I've tried several variations and continue getting syntax error messages.
Open the VBA-Editor, select the DO, press [F1] this will open the help file to the Do..loop statement help file. You will find your answer there. (^-^)
Do...Loop Statement
YOu should also add the following links to your bookmarks:
>Home Page for MS Access 2010 Developer's Reference

Microsoft Access / VBA Insights Sitemap A wealth of information, tricks, and advice

Last but not least:
-> Respectfully, we ask for there to be only one question per thread. This helps to keep the topic on track and makes it easier for our memebers to find an answer for a given topic.
Sep 20 '13 #12
ADezii
8,834 Expert 8TB
@topdesk123:
Create a New Thread as zmbd suggests, and I'm sure that one of us can point you in the right direction.
Sep 21 '13 #13

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

Similar topics

1
Query help (dates and times)
by: StinkFinger | last post by:
All, Struggling with a query here. table name : holidays fields : holname (format varchar) holdate (format date 2004-01-01) The table is populated with all the holidays for the next 5 years....
PHP
12
negative time() ?
by: Rick | last post by:
I need to store a date prior to the unix epoch, any pointers? -- Rick Digital Printing www.intelligence-direct.com
PHP
5
validating Dates and Times
by: Steve | last post by:
I am currently trying to validate data in an access database. I need to verify that columns containing date information are in the format ddmmyyyy and columns containg time information are in the...
Microsoft Access / VBA
11
Q: Dates and Times Problem
by: Geoff Jones | last post by:
Hi I have a table that has a column with Date types. I am trying to view certain rows in the table using a DataView. Using the filter, I can view the rows with, for example, the date equal...
Visual Basic .NET
12
While loop - print several times but on 1 line.
by: Danny | last post by:
Hello there. I'm creating a little text changer in Python. In the program there is a while loop. The problem is that a while loop will have 1 print statement and it will loop until it gets to...
Python
1
duplicate dates different times
by: dhoward | last post by:
I'm having a certain situation. On my application, if a user has the choice to apply for the same permit more than once but on different times. Now the first application would be 100.00 but any...
Visual Basic 4 / 5 / 6
3
Quick script, Loop keystrokes 'x' times??
by: gviteri | last post by:
I apologize for not researching this information on my own but I am at work and need a quick fix. I am running WRQ Reflections built on Visual Basic. I need to create a macro that first inquires...
Visual Basic 4 / 5 / 6
1
validating dates and times
by: =?Utf-8?B?cmF1bGF2aQ==?= | last post by:
Hi: (I know there are lots of different ways to do it) no regex, please if we have string date yyMMdd "080230" whats the best way to test for valid date ? Feb will never have 30 days, 2) if...
C# / C Sharp
4
vikas251074
Loop runs indefinite times
by: vikas251074 | last post by:
Loop runs indefinite times. suppose v_router = 5 is entered by user, then following code runs indefinite times i.e. record is inserted indefinite times though each have different i and v_ip_address....
ASP / Active Server Pages
15
How to compare 2 dates and times to get duration
by: jacc14 | last post by:
I am creating a time sheet and want to compare 2 dates and times to find the length of time taken. 1. I have a start date in one field and start time in another. I then want to enter a finish...
Microsoft Access / VBA
0
How to turn on java script in a villaon keypad mobile phone
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
Java
0
Merging data from multiple Excel files
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
Data Management
0
Migrating Website to Cloud - Emmanuel Katto
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
General
0
BarryA
Navigating the Data Structures and Algorithms (DSA)
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
Algorithms / Advanced Math
1
Is that possible of reading the .csv file in column wise and the column have different lengths ?
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
C / C++
0
How to build RAID in BIOS?
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
Computer Hardware
0
The easy way to turn off automatic updates for Windows 10/11
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
Windows Server
0
tracyyun
Discussion: How does Zigbee compare with other wireless protocols in smart home applications?
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
General
0
agi2029
AI Job Threat for Devs
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
Career Advice

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.

BYTES.COM © 2024
About Bytes
Terms Of Use
Privacy Policy
Sitemap
Advertise on Bytes:
Post a Job
Sponsored Posts
Platinum & Gold Sponsors
Hire Now!