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_DataCalcs1), 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!

[SueHopson]

I think I may have located the answer, from the Microsoft forums:
When used in a query expression, however, Nz always returns a zero-length string.

I really need to learn VB apparently :D For now, the WeekTotal is the last calculation for this database so I'll stick with the solution I've got, although not ideal for the reasons you've pointed out. I can't say this enough, but guys have been awesome and I love the knowledge and support on this site!

[cactusdata]

> Is there a simple way to return a 0 value for the day if no times were reported on that day?

Indeed:

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

[NeoPa]

SueHopson:
I think I may have located the answer, from the Microsoft forums:

Nice one! I think I may remember that now you've brought it to my attention again. Completely forgot that one. Nice find and thanks for posting back.

[isladogs]

Actually that article states:
NOTE: If you use the Nz function in an expression in a query without using the ValueIfNull argument, the results will be a zero-length string in the fields that contain Null values.

As stated, using Nz(Fieldname) in a query will result in a ZLS as will Nz(FieldName,"")
However if you use Nz(FieldName,0) in a query the output for null values is 0 - a number

[NeoPa]

Ah, but the text Sue quoted is also there further down the article. Standing on its own and looking, for all the world, like a definitive statement explaining how the function behaves in those circumstances. It's poorly expressed and very misleading so I responded to the "Did this help?" prompt to express dissatisfaction (Clearly explaining why of course).

NB. It's always good practice to respond to these where possible as it gives those managing the site the feedback required to enable them to find any problems. "Yes. It helps." responses are also very valuable of course so use that as often as you can when a page has helped you.

NB. Obviously that does change the matter here somewhat. We're back to expecting the results to be numeric unless something is wrong and/or different from what's been posted in this thread.

[cactusdata]

> It's poorly expressed and very misleading ...

Could you do me the favour to point to where in the page this is expressed with the exact quote and why/how this is so misleading?
The current wording is mine and is a vast improvement over the previous version which was incomplete and obtuse.

[isladogs]

Hi Gustav
Good to know the author! It would be good to have that info included with each article.
Anyway, the quote is from the remarks section:

Whilst you have correctly qualified that statement elsewhere in the article (including the following sentence), I agree with @NeoPa that, on its own, the highlighted sentence is misleading.

Personally, I think this would be clearer
When used in a query expression without the optional ValueIfNull argument, Nz always returns a zero length string

EDIT:
However, I just created a query to add 3 number fields as a check using Nz with & without a default zero value. The results are identical in each case.

SELECT ID, N1, N2, N3, 
Nz([N1])+Nz([N2])+Nz([N3]) AS NzTotal, 
Nz([N1],0)+Nz([N2],0)+Nz([N3],0) AS NzZeroTotal
FROM Table1;

Based on that simple test, I'm not convinced that even the amended statement is true.
Am I missing something here, Gustav?

[NeoPa]

IslaDogs:
Good to know the author! It would be good to have that info included with each article.

But it is there. In this case GustavBrock is listed second after o365devx. I happen to know that is the name behind CactusData as I know him from outside of Bytes.com as a clever and well-respected Danish MVP specialising in Access.

You would know it, after his recent post, from the fact that he uses the same icon there as here. I don't usually use names on here without the individuals themselves sharing that information or giving their permission. It's their information to share or not as they see fit. In this case I'm happy Gustav has laid claim to that so I don't need to be so careful :-) There are clues to who I am, and my name - Adrian Bell - in my profile and I'm happy to confirm who I am here so no-one needs to be careful with my identity. Many of you know it already.

Otherwise you've given quite a clear indication of what my problem with the wording is. Sue & I, separately, have both come away with a wrong understanding from reading the article and assuming it meant one thing when it meant something slightly different - but in such cases clarity is very important.

I mentioned earlier that you happen to be Danish. I doubt most people would even have realised you weren't English from your various posts.

CactusData:
The current wording is mine and is a vast improvement over the previous version which was incomplete and obtuse.

I have no doubt that is true my friend. Apologies for treading on your toes. I was unaware you'd been involved. Not that that would have changed the situation much but I may have expressed it more gently and shown more respect for the good work you've done in a language that isn't even your main one.

If I were to word it then I would say something very similar to IslaDogs' version :

When ValueIfNull is omitted within a query expression, however, Nz() always returns a zero length string (ZLS).

[SueHopson]

NB. Obviously, that does change the matter here somewhat. We're back to expecting the results to be numeric unless something is wrong and/or different from what's been posted in this thread.

So from the Builder shot below, is the problem linked to the coding of the variant expression? Even though the 0 (cursor position) should be highlighted as the valueifnull expression, it's still reflecting as part of the variant?

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

On a side note, I also tested CactusData's NZ code and it works perfectly (Woohoo!). I'm just curious as to whether or not there is a solution to the DateDiff code we've been using.

Builder.png

[cactusdata]

As for the docs, I think I understand what you mean:

However, the line following the code example block is not a general statement, it refers directly to the code examples above and these only.
And you were supposed to read on ... yes, I know, we don't always bother with the long story.

I can also spot a couple of missing spaces, so I will have it corrected. Thanks for the pointer.

[cactusdata]

Sue, your problem is, that DateDiff fails for Null values. You can check for Nulls and ignore the expression, but this it where it starts to become convoluted:

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

[NeoPa]

Hi Sue.

As far as I know from what you've shared - so only as much of the information as that - I would expect that (Both from the code posted as well as the picture) to result in a numeric value.

If that is not the case then I expect it's from somewhere in the database that hasn't been shared yet. If you'd like me to look into it for you further then please attach a sanitised copy of your database that will work on another system (No good if it uses linked tables to other databases I don't have.) and I'll look at it for you and try to determine why the result is alphanumeric.

This can be done within the thread if you're comfortable to, or in a Private Message if you prefer. I am often relied on to keep data secure and unshared but if any of it's proprietary then please get the permission of the owner first. I'm happy to confirm I'll treat it securely if required.

Also please include instructions to enable me to find the issue within your database. I'll leave that with you.

[NeoPa]

CactusData:
Sue, your problem is, that DateDiff fails for Null values.

That really depends on what you mean by fails. As far as I was able to tell from my tests what actually happens is that it returns a Null value. This Null value is handled by the formula using Nz(). This doesn't seem to me to explain why the value returned is a string (ZLS) instead of the 0 (Zero) as specified.

[isladogs]

Gustav (@cactusdata)
I've read the article again and specifically the section you showed in post #25

I agree completely with this section:
If the Value of the variant argument is Null, the Nz function returns an unassigned Variant, the special value Empty. In VBA, Empty evaluates to the number zero or a zero-length string, depending on whether the context indicates that the Value should be a number or a string.

However I still don't accept your subsequent comment:
When used in a query expression, however, Nz always returns a zero-length string.

I always use a default value so hadn't really thought about this until now. But plenty of experience indicates otherwise
Indeed if you look at the results I showed in post #22, whether or not a zero default value is provided, when summing number fields in a query each null value evaluates to zero NOT a ZLS

Ade (@NeoPa)
I recognise Gustav's distinctive avatar everywhere I see it and am well aware of various names he uses at different forums.
However, I confess to never having noticed the various icons/avatars under the title line in MS documentation articles, nor had I ever clicked the '+3' button to see the list of names.
Amazing how such obvious things can be completely overlooked. I expect from now on, I'll always look to see who the authors are

Regards
Colin

[SueHopson]

Will do on Monday Neo
Will PM you will the copy. Have a great weekend everyone!

[cactusdata]

> DateDiff fails for Null values.

I had DateValue in mind, sorry.
DateDiff returns Null, thus Nz works as expected on the entire expression, and 0 (zero) is returned for any value being Null.
Fine, but then I don't see what Sue's problem is?

[NeoPa]

CactusData:
Fine, but then I don't see what Sue's problem is?

You & me both brother!

I've asked Sue to PM me the database in a working form so I can have a look. Often these issues are related to something that hasn't been included, and the OP is generally not even aware of. We'll see when it gets here.

[isladogs]

@cactusdata
Just to clarify, are you also going to correct statements like this in the online article which are not true for number fields:

NOTE: If you use the Nz function in an expression in a query without using the ValueIfNull argument, the results will be a zero-length string in the fields that contain Null values.

[NeoPa]

Well. That's strange. I can confirm what Sue reports in that the values returned from the Nz() expression in the query are indeed treated as strings. Quite bizarre when you consider the documentation (And I'm referring to the tried & trusted version that came with Access 2003 just as much as the recent versions Gustav).

The best I can make out is that when Nz() has a parameter that is itself indeterminable, so an expression (Especially one that contains an IIf() reference.) rather than an actual field reference, then the query has no way of knowing what type of result to expect. It may be alpha for some records but numeric for others. In such a scenario I assume it decides to play safe and treat all as strings.

Thus it is that the results are interpreted, within the query even if returned by Nz() as numeric, to be strings. Thus the later expression to add (+) two values together defaults to treating them as strings even though the actual values were probably quite validly numeric.

Where does this leave us?

With a very interesting question to be sure. Also with a need to ensure the query understands clearly that the values are expected to be treated as numeric values - which we know they are because we understand what's in the expression but we can't really expect Access (Jet/ACE) to determine for itself.

Thus the extra wrapper of Val() is required when, to our simple understanding, it shouldn't be needed.

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

A nuisance, and painful, but it seems unavoidable in the circumstances. If you were to use a numeric field reference in Nz() instead of an expression then you'd see what you'd expect from what's been discussed previously. I tested with [EID] and got exactly as you'd expect.

[cactusdata]

I did a test, and it seems like Nz behaves the same in VBA and in queries, so the docs are in need of a correction - I just haven't had the time.

The key to the understanding is, that Nz without the second parameter returns the very special Empty for Null values - not an empty string, not a zero number; and Empty behaves as illustrated in the black box above snipped from the docs.

[NeoPa]

Hi Gustav.

You should understand that in none of the code we've been discussing from Sue has the second parameter ever been empty (Not passed).

The data we're seeing as a problem is from records where the data actually does exist, so the Empty value doesn't come into this scenario.

[NeoPa]

FYI:
A new thread (Adding Unbound Data to Table) has been created that deals with matters relating to this one. Feel free to have a look and respond via the link.

[Rabbit]

The vartype function should help you diagnose what's going on. You may have to break up the expression and test the vartype at different points to figure out where it becomes a string