GLese
Registered User.
- Local time
- Today, 17:37
- Joined
- Feb 13, 2018
- Messages
- 52
Okay, so I've dug around here and found several ways to do this, but none that meet my specific need, or the solution isn't clear enough for me as a new Access/VB user to understand where to implement it.
My dbase has a table to track samples that come into our chemical lab for QC approval. The main function of the dbase is to allow us to track KPIs, with one being Turn Around Time. We consider TAT (in hours (1.00))=Time Logged Out - Time Logged In. Simple enough.
The tables containing this data have a column DateTimeIn and DateTimeOut, each of these are filled in based on button clicks on a form. They are formatted as General Date, and populated with =Now().
I know I could use DateDiff with a result in minutes, then math it out to a result in hours. However, we do not include non-business hours (5:00pm-8:00am on weekdays, all hours on weekends, and company holidays) in our current calculation in the excel spreadsheet we currently use to log samples in.
Am I better off creating a VB script that then gets plugged into a query to calculate TAT?
Should I use a complex expression in a query field?
TL;DR: What's going to be the best way to get to a query result which gives me a calculated TAT based on our parameters for each sample entry?
(As a note, I'm not necessarily looking for a written out code unless someone has it, but if y'all can point me in the right direction, I am thoroughly enjoying the learning process I'm going through with Access/VB)
Thanks in advance y'all!
My dbase has a table to track samples that come into our chemical lab for QC approval. The main function of the dbase is to allow us to track KPIs, with one being Turn Around Time. We consider TAT (in hours (1.00))=Time Logged Out - Time Logged In. Simple enough.
The tables containing this data have a column DateTimeIn and DateTimeOut, each of these are filled in based on button clicks on a form. They are formatted as General Date, and populated with =Now().
I know I could use DateDiff with a result in minutes, then math it out to a result in hours. However, we do not include non-business hours (5:00pm-8:00am on weekdays, all hours on weekends, and company holidays) in our current calculation in the excel spreadsheet we currently use to log samples in.
Am I better off creating a VB script that then gets plugged into a query to calculate TAT?
Should I use a complex expression in a query field?
TL;DR: What's going to be the best way to get to a query result which gives me a calculated TAT based on our parameters for each sample entry?
(As a note, I'm not necessarily looking for a written out code unless someone has it, but if y'all can point me in the right direction, I am thoroughly enjoying the learning process I'm going through with Access/VB)
Thanks in advance y'all!