Code for Business Hours -Help Please

[Gasman]

Hi Lilly. Not sure why you get 2, I get 4 as shown below:

FWIW, just as a heads up, this was happening with arne's code.
I was getting 'correct' values for certain dates using his code. the o/p was getting something completely different.?

 

[theDBguy]

FWIW, just as a heads up, this was happening with arne's code.
I was getting 'correct' values for certain dates using his code. the o/p was getting something completely different.?

Right. I was also wondering if there's something different about her computer.

 

[Mark_]

Lilly,

Since the start/end times are variable for your employees, you may find it most useful for a given extract to have one (or more) "processing" records. Each would have start date/time and you simply sum them for your total. This avoids issues with "Well, we started at 4pm so its supposed to know for THIS one that were here until 5pm, but on the other one we started it at 3pm but its supposed to know we stopped work at 4pm, even though BOTH are finished up at 8:30 am".

Instead one extract has two records showing "Start 4pm, end 5pm" and "Start 8am end 8:30am" while the other has "Start 3pm, end 4pm" and "Start 7am, end 8:30am". That way you don't have to try to figure out who's got what schedule when, you just record each period they work.

 

[Lilly420]

Hi,

I am home, opened Access on my home computer, just a blank database, created a module and added your code and it is working...so my question is … could there be something wrong with my Database...I wonder, that I am using at work...do you have any suggestions?

? TotalWorkHours (#8/1/2019 8AM#, #8/2/2019 9AM#) 9
? TotalWorkHours (#8/1/2019 10AM#, #8/2/2019 11AM#) 10

I won't be able to see your responses as these emails go to my work email but will log on tomorrow early from my work laptop and check to see if there are any responses...but I am a little concerned now.

Thanks for helping me and for all your patience, I appreciate it.

Lilly

 

[arnelgp]

ok, open for business again...

suggested rules:
1. extraction always starts at #9 am# and will be computed for 8 hrs.
2. on first day of extraction, any extraction done Before #9 am# will be taken and computed for 8 hrs only.
3. on first day of extraction, any extraction done After #9 am# (10am-onward), the ending time will be #5 pm#.

4. when continued the next day, when extraction time ends between #7 am# and #9 am#, credit hrs will always be 1 hr.
5. when continued the next day, when extraction time ends after #9 am# (10am-onward), the starting time will be #8 am#.

6. extraction expands more than 1 day, same rule (rule 4 & 5) applies for the first day and last day of extraction.
7. in between dates shall automatically computed as 8 hrs.

for your review and revision.
if all is ok, then let's do it.

 

[theDBguy]

Hi,

I am home, opened Access on my home computer, just a blank database, created a module and added your code and it is working...so my question is … could there be something wrong with my Database...I wonder, that I am using at work...do you have any suggestions?

? TotalWorkHours (#8/1/2019 8AM#, #8/2/2019 9AM#) 9
? TotalWorkHours (#8/1/2019 10AM#, #8/2/2019 11AM#) 10

I won't be able to see your responses as these emails go to my work email but will log on tomorrow early from my work laptop and check to see if there are any responses...but I am a little concerned now.

Thanks for helping me and for all your patience, I appreciate it.

Lilly

Hi Lilly. Whenever there's inconsistent results when dealing with dates information, my first response is to check the Regional Settings of the computer. If it's not that, then the next step is to check/verify the data.

 

[Lilly420]

Good morning all,

I did check the regional settings and the are correct.

I decided to create a new database, and imported my objects from the other into it just in case something was wrong with the one I was using. I also created a new module and copied the code again within the new database. Using the code supplied by DBguy, business hours seem to be calculated correctly so THANK YOU ALL for your help and guidance. You are amazing people, and I truly appreciate you all.

Lilly

 

[theDBguy]

Good morning all,

I did check the regional settings and the are correct.

I decided to create a new database, and imported my objects from the other into it just in case something was wrong with the one I was using. I also created a new module and copied the code again within the new database. Using the code supplied by DBguy, business hours seem to be calculated correctly so THANK YOU ALL for your help and guidance. You are amazing people, and I truly appreciate you all.

Lilly

Hi Lilly. Glad to hear you got it sorted out. We were all happy we could assist. Good luck with your project.