The result of both forms may or may not be the same.The point is, the field-specific form won't consider null values in its count.That means that you can't apply this solution to existing records.The truth is, it's usually easier to handle null values than it is to usurp them in this fashion.To find or exclude null values, use Is Null and Not Is Null, respectively, in criteria expressions and SQL WHERE clauses.For instance, to find null values in a query, you'd enter Is Null in the appropriate field's Criteria cell.However, Count(), First(), and Last() do evaluate null values.It makes sense that they would—just because one field contains a null value doesn't negate the row's purpose within the context of the domain.
Instead, Jet sorts null values to the top or the bottom of the result set, depending on the sort order. However, too many null values often point to an unnormalized table.Instead of returning just the Subtotal, as you might expect, the expression returns Null. If your data contains null values, use the Nz() function to protect your expressions from this error. That's true, as long as you're using native functions and VBA.That's because any equation that encounters a null value will always return Null. Specifically, Nz() returns a value other than Null when it encounters Null as follows: In this case, Nz() returns 0 when Shipping equals Null. It isn't true if you're manipulating data via the Active X Data Object (ADO) library.For instance, Count(*) counts the total number of rows in a recordset even if some of those rows contain null values.If you want to exclude null values in a count, specify the field in the form Count(field).If a non-native library returns an error when working with null values, this switch will probably do the trick. The good news is, there's a bit of reason to the inconsistency.