Need advice on Time Calculations

[Sean75db]

Hello
This may be a long question but it is a very big part of my DB. If i am not clear i am sorry i am still learning VBA and have to get the full grasp on it.
I work for a bank and have build a few little DB's for differant groups but my current project is a work flow tool for a department. I have it 90% completed and i still have to build in the reporting side. Part of the reporting is caclulating times and this is where i am having problem.

I have some coding built but i am getting inconsistant results, as an example if i am trying to calculate the total amount of time between [start Time], [End Time] then minus any [Stop Time].

Me.Total_Time = Format(Int(Workhours(DateAdd("n", Nz([Total_Stop_Time]), [Processor_Date_Stamp]), [Date_Sent_to_RM])), "00") & ":" & Format(Int((Workhours(DateAdd("n", Nz([Total_Stop_Time]), [Processor_Date_Stamp]), [Date_Sent_to_RM]) * 3600 - (Int(Workhours(DateAdd("n", Nz([Total_Stop_Time]), [Processor_Date_Stamp]), [Date_Sent_to_RM])) * 3600)) / 60), "00") & ":" & Format(((Workhours(DateAdd("n", Nz([Total_Stop_Time]), [Processor_Date_Stamp]), [Date_Sent_to_RM]) * 3600 Mod 60)), "00")

I have this on a command button, i have this same code for a few other calculations and in some cases it seems to be working i have just changed my start and stop fields along with what stop time to take out in each case. I have attached some parts of my DB, one of the modules, my main table and the form where i am running the coding.

Thanks

 

[TJPoorman]

I can't take a look at your db right now as I'm rushed for time, but have you looked into the DateDiff() function?

 

[Sean75db]

Yes i have, to be honest i don't rember why i didn't use DateDiff() but i think it had to do with DateDiff() not calculating my stop time in the expression. I have that coding in several places and they work right. in the example my [Total_Time] Calc is one of the ones that isn't working

 

[TJPoorman]

Is there a specific reason you couldn't do something like DateDiff("n",[Start],[Stop])-(Calculation for total stop time)

This would be the total minutes between start and stop less total time stopped.

 

[Sean75db]

Thanks for the help TJPoorman, i figured out why i was having issues. It wasn't a problem with the calculation. It was me, when i changed to differant start time fields i didn't referance where the field was since it wasn't on the form i was trying to run the calculation.