Adding Time in Calculated Time Fields returning as Strings

[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