It's sunny again today! When I left yesterday afternoon I was super close to getting my VBA code with the embedded SQL to work, or so I thought. Turns out that the reason my code wouldn't run was based on many, many problems that I had to debug including, but not limited to, the fact that spaces are important to watch for when inserting SQL that is multiple lines, DoCmd.RunSQL won't work for SELECT statements, and the database I was using to get my data was actually another query (Access uses like views) so I had to find some clever way to get VBA to allow me to do that.
If I tried to give credit to every website I got help from to create my VBA code, I feel like I would be citing the whole internet! Basically, what I ended up doing was calling my existing query that created the variables I needed, and then creating a brand new query in VBA that was based off the previous query's data. My sub will in the end output both queries, but I'll only really be interested in the last one. When I finally got my VBA code to work and do this (granted with my smaller trial data) I felt like jumping for joy! However, it's still not completely functional yet because my first attempts were with a static given hour, and I want to be able to automate it for it to compute the results for every hour in a given day using a loop. Unfortunatley, my first attempt at this showed that even just going through the loop twice made Access take forever and crash.
Before my meeting with Anat, I got it to go in a loop by making it actually create a new query each time for every hour. This means that I'll have to take the data from the 24 queries and manually put them nicely into one Excel spreadsheet, but that should not be too time consuming or difficult.
Before my meeting with Anat, I got it to go in a loop by making it actually create a new query each time for every hour. This means that I'll have to take the data from the 24 queries and manually put them nicely into one Excel spreadsheet, but that should not be too time consuming or difficult.