Adding Time in Calculated Time Fields returning as Strings

[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.

[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!