1 of 1 people found this helpful
This might work: MIN(IFNULL([Field1], NULL), IFNULL([Field2] ,NULL ))
I tested its validity, but I didn't test its result. Let me know if it does what you're looking for.
Shawn - I think I was working on this the same time as you, here's what I found out:
From trying different values in the MIN() expression, it appears that Null evaluates to "lowest value" as compared to any other number, positive or negative. In other words, a MIN() with a Null in in it will always return Null. So, here's an alternative:
MIN(IF NOT ISNULL([Field1]) THEN [Field1] ELSE 1e30 END, IF NOT ISNULL([Field2]) THEN [Field2] ELSE 1e30 END)
The 1e30 is there because we don't want the expression to evaluate to something that would be an answer, so set that as high as you need to.
Jonathan, thanks for the correction and info. (I was being lazy and not doing the testing before posting.)
Thank you Shawn and Jonathan for your guidance.
I need to correct my original post: in Access the function: min(nz(Field1),nz(Field2)) would also return null as the minimum value.
I was using the nz function to allow the SUM of multiple fields that would contain null value but it won't work with MIN.
Anyway, thank you very much for the recommendation!
1 of 1 people found this helpful
it appears that Null evaluates to "lowest value" as compared to any other number, positive or negative.
Not exactly. Null means missing value, or no information, or maybe even not applicable. A null value doesn't have a defined order when compared to a non-null value.
Remember there are multiple types of calculations: row level (normal) calcs, aggregate calcs, table calks.
Normal calculated fields return a value for each row in the data. You can think of them as adding an extra column in the data source defined by a formula that can only refer to other cells in the current row. Aggregate calculated fields return one value for a block of rows, which is why they need to use aggregation functions to reduce a set of values to a single result.
From my experience and testing, null values are ignored by aggregation functions. So Sum() or Avg(), for example only consider the non-null values when aggregating over a set of rows.
For most row level functions, if one of the arguments is null, the function returns null. So Sin(null) is null. There are exceptions -- zn(), isnull() and iif() can return non-null values, even if one of the arguments is null. For example, iif(1<2, 3, null) returns 3 even though the last argument is null.
If you've used SQL for a while, these rules should look familiar.
Min() and Max() are special in that they can act as either row level functions or aggregate functions, depending on the number of arguments. min([my_field]) is an aggregate calculate field that will ignore null values, and will return null if all the values are null. min([my_field], 3) is a normal row level calculated field that will return null for those rows where [my_field] is null, and either [my_field] or 3 for the other rows.
BTW, if you want to return the min of two fields, but don't want to return null when only one of the fields is null, here's an expression that does not depend on an arbitrary large number. You are in effect taking responsibility to define the semantics in the case mixing nulls and non-null values.
if isnull([Field1)] then
elseif isnull([Field2]) then
I'll let someone else discuss table calcs
Alex, thanks for the clarification and alternative calculation!