When calculating formulas as query columns, avoid null data.
Null Values Can Throw Off Your Queries
I love the flexibility of Access queries, they provide a wide range of options to do many wonderful things. Recently, I was working on a project for a client were I was using multiple fields in a table to do calculations when I noticed blanks in some rows where did should have been values. That's when I discovered null values can throw off my calculations.
My calculations were straight forward, the table had columns for PremiumReceived and Refunds. I wanted to calculate the net received by subtracting the Refund amount from the PremiumReceived:
NetReceived: [PremiumReceived] - [Refunds]
Net received was a calculated column in my query and not a column in the table, which was a linked SQL Server table. The problem was that sometimes there was a value of 0 in the Refunds column and sometimes there was a null, (nothing). Thus, some records were null even if the PremiumReceived was greater than zero, because Refunds was null.
Use Default Values to Solve the Problem
I fixed the issue by using an update query to replace all null values in Refund to zero, modifying the SQL Table properties so that the default value is zero then refreshing the link in Access.
If you can't modify the table properties, another solution would be to modify the formula in the query to:
NetReceived: iif(isNull([Refunds], [PremiumReceived] , [PremiumReceived] - [Refunds])
|
