Adding Time in Calculated Time Fields returning as Strings

[SueHopson]

I'm sure this is a simple solution to all you fabulous pros out there, so I am only going to include 2 days of the week in my sample code, rather than the full weeks' days. Alternate solutions/suggestions are appreciated, as I am used to only coding within the queries themselves but would love to expand my knowledge.

Employee Data Table
IN-Mon Date/Time [Format Medium Time]
OUT-Mon Date/Time [Format Medium Time]
LCH-Mon Yes/No

IN-Tue Date/Time [Format Medium Time]
OUT-Tue Date/Time [Format Medium Time]
LCH-Tue Yes/No

You get the idea...

In my first Query (qry_sbfm_DataC alcs1), I am using to following code to determine the number of hours worked each day

MonTotal: IIf([LCH-Mon]=-1,((Format((DateDiff("n",[IN-Mon],[OUT-Mon])\60),"Fixed")-0.5)),(Format((DateDiff("n",[IN-Mon],[OUT-Mon])\60),"Fixed")))
TueTotal: IIf([LCH-Tue]=-1,((Format((DateDiff("n",[IN-Tue],[OUT-Tue])\60),"Fixed")-0.5)),(Format((DateDiff("n",[IN-Tue],[OUT-Tue])\60),"Fixed")))

This works fine and returns the number values I am looking for. When I look to add these values, however, I am being returned a string value rather than the numeric total.

WeekTotal: [MonTotal]+[TueTotal]

This returns, 9.59.5 as opposed to 19. I did try reading around the forum and tried CDate, etc unsuccessfully (as in I don't really understand what they do or how to use them...)

Since the WeekTotal will be used in my next calculations I need it to reflect a number of hours, as opposed to the string. Here is the code, so you can see where I'm headed.

RegHrs: Format(IIf([WeekTotal]>44,44,[WeekTotal]),"Fixed")
OTHrs:Format(([WeekTotal]-[RegHours]),"Fixed")

As always, I appreciate your time/guidance.

[isladogs]

Using Format ALWAYS returns a string so 9.5 & 9.5 will become 9.59.5

If you need to add the string 'numbers' you need to 'cast' those back to numbers.
For example

WeekTotal: CDbl[MonTotal])+ CDbl([TueTotal])

OR better still, use the Val function

WeekTotal: Val[MonTotal])+ Val([TueTotal])

OR if possible avoid creating the string values in the first place

[NeoPa]

I'm guessing here Sue. but it seems to me you may be using the Format(X,'Fixed ') approach here in order to round the values to a set number of decimal places. I'm further guessing you didn't use the standard Round() function because of its irritating approach of Bankers' Rounding or Round-to-Even? The approach I see you using is a bit belt-and-braces if I may say without offense.

You calculate the whole hours (to the nearest 30 seconds) to an integer value, then you use Format() to round it to an integer - the already integral value - then you lose half an hour if [LCH-Mon] (is True).

EG :

MonTotal: DateDiff('n',[IN-Mon],[OUT-Mon])\60-IIf([LCH-Mon],0.5,0)

NB. For Boolean (Yes/No) values it makes little sense to compare them to create a Boolean result; they are already that to start with. See example.

Also, if you want to round down to complete hours then using seconds is more accurate :

MonTotal: DateDiff('s',[IN-Mon],[OUT-Mon])\3600-IIf([LCH-Mon],0.5,0)

[cactusdata]

You can simply subtract the times and multiply with 24 to obtain the hour count:

MonTotal: ([OUT-Mon] - [IN-Mon] - Abs([LCH-Mon] * #00:30#)) * 24,
TueTotal: ([OUT-Tue] - [IN-Tue] - Abs([LCH-Tue] * #00:30#)) * 24,
... etc.

[twinnyfo]

I think CactusData is on the right track. I might make one minor change, which is a hybrid of his and NeoPa's recommendation:

MonTotal: (([OUT-Mon] - [IN-Mon]) * 24) - IIf([LCH-Mon], 0.5, 0)

Either way, I think sticking with simple subtraction of the actual data values prevents multiple calculations. Depending on how you format the result will get you hours, minutes or seconds (or all three!).

Hope this hepps!

[SueHopson]

Thank you everyone - these are all amazing expressions, and seeing them in front of me I understand the code but never would have discovered them on my own. Isladog, thank you for explaining the behaviour of Format and NeoPa I never take offense when my coding skills are, in fact, very much "belt-and-braces", but you were right about the Fixed vs Rounding logic.

I'm going to implement each of these suggestions today and see how each works (as part of my learning curve). The logic behind NeoPa's response (using my original DateDiff code) to CactusData's simplified subtraction and Twinny's combination of the two is clear and concise. I'll let you know which works best for me soon.

[NeoPa]

All good Sue. Let us know how you get on :-)

@CactusData.
If the aim is to include only whole hours (which was what introduced the original complications) then that isn't reflected in your logic as far as I can see.

[SueHopson]

Thank you all. The code provided did exactly what I needed it to do in all instances. I have elected to go with NeoPa's solution as it best reflects what I am looking for. However, I have now encountered a new problem, despite trying all the codes above, that is related to the day results being null.

When adding the WeekTotal, this works fine, so long as a value is calculated for each day. But, only a few, select people actually work weekends and therefore the WeekTotal is not calculating when any single day is returned null. Is there a simple way to return a 0 value for the day if no times were reported on that day?

MonTotal: DateDiff('n',[IN-Mon],[OUT-Mon])\60-IIf([LCH-Mon],0.5,0)

WeekTotal: [MonTotal]+[TueTotal]+[WedTotal]+[ThuTotal]+[FriTotal]+[SatTotal]+[SunTotal]

I know I can make this work but was wondering if you have another suggestion to keep the code cleaner

MonTotal: Iif(DateDiff('n',[IN-Mon],[OUT-Mon])\60-IIf([LCH-Mon],0.5,0) Is null,0,(DateDiff('n',[IN-Mon],[OUT-Mon])\60-IIf([LCH-Mon],0.5,0)

[NeoPa]

Hi Sue.

The simple answer is to use the Nz() function :

WeekTotal: Nz([MonTotal],0)+
           Nz([TueTotal],0)+
           Nz([WedTotal],0)+
           Nz([ThuTotal],0)+
           Nz([FriTotal],0)+
           Nz([SatTotal],0)+
           Nz([SunTotal],0)

Even simpler, and probably more reflective of reality, is to prepare each day's value that way so it needn't be done in the WeekTotal formula :

MonTotal: Nz(DateDiff('n',[IN-Mon],[OUT-Mon]),0)\60-IIf([LCH-Mon],0.5,0)

[twinnyfo]

However, I will mention this, just as an overall recommendation, even though it is not directly related to the specific question at hand. At Bytes, we still try to give good advice to help users develop their projects based upon sound principles.

It appears that these fields are part of a "wide" table, that is, it contains the following fields: [IN-Mon], [OUT-Mon], [LCH-Mon], [IN-Tue], [OUT-Tue], [LCH-Tue], ... [IN-Sun], [OUT-Sun], [LCH-Sun]. Correct? As an overall design change, I would recommend a "tall" table, with the following Fields: [Employee], [WorkDate], [Time-IN], [Time-OUT], [LCH]. This also makes it easier to track employee in and out hours: you set the employee, date, time in and time out.

Then, your query provides a sum for any week you may choose:

SELECT 
    [TableName].[Employee], 
    [Start Date], 
    Format(Sum((([TableName].[Time-OUT] - [TableName].[Time-IN]) * 24) - IIf([TableName].[LCH], 0.5, 0)), "0.00") AS WorkWeek 
FROM 
    [TableName] 
WHERE 
    [TableName].[Time-IN] >= #[Start Date]# 
        AND [TableName].[Time-OUT] < #[Start Date] + 7#
GROUP BY 
    [TableName].[Employee], 
    Format(Sum((([TableName].[Time-IN] - [TableName].[Time-IN]) * 24) - IIf([TableName].[LCH], 0.5, 0)), "0.00");

(this was written free-hand, but I think it will work)

This method prevents the calculation of non-existence work days and is more in line with better DB design principles.

Some notes: [Start Date] is a date, not a time. So, if [Start Date] is 2021-01-04, any employee who begins work on or after midnight (morning) of January 4, will get captured. The end of the employee's work week may begin prior to Midnight on January 11 and those hours would be added. However, this method prevents double billing of time, as that same employee's hours beginning at 10 PM on January 10, but ending at 6 AM January 11 would NOT get counted for the week beginning January 11. I hope this all makes sense.

Again, not directly related to this question, but, hopefully, some good advice.

Just some fodder to chew on. Hope this hepps--or you can ignore it.

:-)

[SueHopson]

NeoPa,
Thank you once again! I've added the Nz function to each DayTotal as I agree it will be more effective, although I did have to update the code to get it to work properly:

MonTotal: Nz(DateDiff('n',[IN-Tue],[OUT-Tue])\60-IIf([LCH-Tue],0.5,0),0)

However, using the Nz function did return my WeekTotal calculation as a string once again. I used isladogs suggestion from her original response and adjusted the WeekTotal Code and everything appears to be working properly.

WeekTotal: Val([MonTotal])+Val([TueTotal])+Val([WedTotal])+Val([ThuTotal])+Val([FriTotal])+Val([SatTotal])+Val([SunTotal])

On a side note, I don't know how long it takes someone to memorize every code abbreviation in Access, what it does, and how it returns data, but this is why you folks are the pros! What I have learned over the last few days will greatly help.

Twinnyfo,
That's some great fodder, and something I will chew on for future projects. I can definitely see the advantages of your suggestion.

[isladogs]

Hi
Just to say, you're probably not a boy called Sue (as in the Johnny Cash song) and I'm not a she .... though my dog Isla in the photo is.... :~)

Regards
Colin

[NeoPa]

SueHopson:
However, using the Nz function did return my WeekTotal calculation as a string once again.

That's bizarre. It certainly shouldn't, going by the code I posted and that you've posted back. That will necessarily return a numeric value. If that isn't working as it should then you should really fix that rather than bodge your way round it. Bodges have a nasty way of breaking further down the line if you don't get them fixed properly.

SueHopson:
On a side note, I don't know how long it takes someone to memorize every code abbreviation in Access, what it does, and how it returns data, but this is why you folks are the pros!

Pros are those who know that the information about what means what is only ever a few clicks away generally. Sure, I remember much of the stuff after a while, but I still check values regularly.

[SueHopson]

Nice to meet you Colin, and according to my ultrasound I was supposed to be a boy... you can imagine my parent's shock :P

[isladogs]

Ha! At least they didn't stick with whatever name they'd originally planned for you!