Access sql return 0 if null

For instance, a simple expression such as. Instead of returning just the Subtotal, as you might expect, the expression returns Null. That's because any equation that encounters a null value will always return Null.

Although it's a nuisance, it makes sense. You can't evaluate an unknown value. If your data contains null values, use the Nz function to protect your expressions from this error. Specifically, Nz returns a value other than Null when it encounters Null as follows:. In this case, Nz returns 0 when Shipping equals Null. Use Nz in criteria and VBA expressions. Access projects don't support Nz. In 3, you learned that Null doesn't equal anything.

That's true, as long as you're using native functions and VBA. For instance, the following statement executed against an ADO Recordset object returns an error:. That's because ADO doesn't recognize the Is operator in this context. The ADO library supports the Equals and Inequality operators when searching for or excluding null values.

Fortunately, the correction is as simple as replacing the Is operator with the Equals operator:. You'll find Access a bit of an oddball on this issue. Many libraries use the Equals and Inequality operators instead of Is. If a non-native library returns an error when working with null values, this switch will probably do the trick. Not all aggregate functions consider null values.

The good news is, there's a bit of reason to the inconsistency. An aggregate function that evaluates a field does not evaluate null values in its result. 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.

If you want to exclude null values in a count, specify the field in the form Count field. 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.

Otherwise, Jet excludes the row from the results. This behavior is inherent in the equality issue discussed in 3. Because Null doesn't equal anything, it can't satisfy a condition other than Is Null. For instance, the simple expression. But this function's a lifesaver if you need to create calculated fields that work with values that could be null.

Consider this innocent-seeming example:. This expression runs into trouble if Quantity is null. Nulls have a strange way of spreading, somewhat like an invasive fungus.

If you have a null anywhere in a calculation, the result of that calculation is automatically null. In this example, that means the OrderItemCost for that record becomes null. Even worse, if the OrderItemCost enters into another calculation or a subtotal, that too becomes null.

Before you know it, your valuable query data turns into a ream of empty cells. To correct this problem, use the Nz function to clean up any potential nulls in optional fields:. Finally, you can use Nz to supply a different value altogether. In legacy data, it is very common that you find a lot of unnecessary NULL values and you need to do massage to present this data, whether it is a report or an email.

Generally, we use few techniques to avoid NULL and replace it with any characters or numbers. Before proceeding with the solution, I would like to create a sample to demonstrate the solution. Comments RSS. You are commenting using your WordPress. You are commenting using your Google account. You are commenting using your Twitter account. You are commenting using your Facebook account. Notify me of new comments via email.