Sum null Results

Hi Guys. I build a report in which i need to add a number and NULL.
like = Sum(# + NULL)

But i should get
=Sum(# + Null) = #
for example:
pages = 100
books = NULL
are too feilds in my report

when i do

=Sum(pages+books) gives me nothing
but i would like to get
=Sum(pages+books) = 100
is there any way i could get this


I have looked and looked and do not see an answer?
If I have a report based on a crosstab query that has many null values.
Here is the breakdown
I am trying to do addition adding up several fields but if any one of the fields has a null value it does not return the sum. Is there a workaround for this.

I am just thinking of dropping in and IIF statement into the fields and returning 0 if they are null but I am wondering if there is a better way.

On my report I have the following fields:

The control source for the report is a qry.

[TotalPECap$] is the total for above fields.
control source for this one is:

My problem is when the query returns no values at all I get blanks in the first 4 fields and #error in the [TotalPECap$]

Although I would like to see "-" in the 4 fields when nothing is returned from the controlling qry , I really want to show $0.00 when the total for these 4 fields is returning null.

So I tried the following in the control source of [TotalPECap$] to force it to show 0, but no, it won't play!


can you please help

I have a query that adds 12 months of forecast data, but some of the data contains nulls. The problem is that if any one of the 12 months contains a null it makes the result null. Is there an easy way to make it look at the nulls as zeros?

The data is on a SQL server, so I have no control of the source data.

- Marc

Is Access slower at summing null records than SQL server?
I have a query which takes less than 1second in SQL server but takes about 5-10 in Access but can't think why there is such a lag in processing speeds.

Hi Access Gurus

Something is driving me bloody mad… no its not the wife.

I have a query whish uses the group by function and sums up based on some invoicing figures (currency)

Now my issue is that I need the sum to show a value of 0.00 if indeed there are no records. (from the Invoicing table)

Is this possible. I guess the query is working as it should is its sees no records to sum up. Its causing issues on my reports.

Appreciate if anyone can assist as this is causing me to have to create records with a value of zero so that it works properly and then it means I have a record that I don’t really want.



See snapshot of the query below

I am currently creating an Access 2007 database for calculating salesperson commissions. I have a table with 5 fields I'm working with: SalesRep, SOWRep, TerritoryRep, Period and Commission. I am trying to build a query that will calculate commission for a salesperson for each record where their name appears in one of the first three fields. Each time their name appears, they get the commission listed in the Commission field for the stated Period. I have managed to do this part successfully. My problem now is that I am creating a query that will sum all of their commissions by Period. I have run into a situation where sometimes a salesperson will not appear in one of the rep fields, resulting in NULL values in the previous queries. How can I create a final query that will sum correctly even if there are NULL values returned on the previous queries?

Here is what I have so far:

November: Avg((SELECT [SumOf1/3GM$s] FROM [BaxterActualMargin1] WHERE [Period] Like "November*")+(SELECT [SumOf1/3GM$s] FROM [BaxterActualMargin2] WHERE [Period] Like "November*")+(SELECT [SumOf1/3GM$s] FROM [BaxterActualMargin3] WHERE [Period] Like "November*"))

I have been trying to figure out the following scenario. Hopefully someone can help.Access 2002

LEADS table has many fields. The ones in question are
ContractAmount Currency
RegionalManager Text
ApptDate Date
DFTDate Date

Report runs for range of [apptdate] Between [begindate] and [enddate]
Grouped weekly on RegionalManager
Displays various totals on other fields in the RegionalManager footer

Now I need a conditional sum of the constructionamount where dftdate is null
This is also in the regionalmanager footer

I have a report comprised of several subreports.

One of the subreports has calculated fields based on a query using using aggregate functions (SUM).

If all the amounts come to zero/null, however, the subreport doesnt show when the main report is produced. This results in a large blank space instead of the report showing all the text with zeros in the calculated fields.

This looks messy and not very professional.

Could someone help please??

In a text box control on a report footer I have the following:

=sum(nz([Carded Capacity]) and it does not work. (I get message that JET could not execute the SQL statement because it contains a field that has an invalid data type).

If I change it to:
=nz([Carded Capacity]) does work. But then I only get the value of the last record.

The Carded Capacity field has nulls. I do not have any problems when I force 0 in where there are nulls - (then I also don't need the nz function). However, I am curious as to why I must force 0's into my table, when I thought the nz function converted nulls to zero's? And why would adding "sum" make it not work? Thank you. Marie

This might be no-brainer for some, but not me. When I created my report I was able to set it to sum certain columns, and that's just peachy, but if I have a column with no value then it automatically puts in zero (the data type is Currency, so it actually puts in $0). You can imagine the problems this causes with a small number of entries. Is there some way, either in the input form, the underlying table, or the query that feeds the report, to allow a field with a zero in it to be treated as a <Null>?


For crosstab queries, when a sum valuates to zero, it returns a null value. Is there anyway that it can return a zero instead of null because I get these blank spaces in the report obtained from the crosstab which would look better with zeros and not confuse the management who reads these reports. Thanks.

I have a textbox on my report with this as the control source...

If the textbox is empty (or null) it displays '#Error'
I would like it to display '0'

Can I use an IIF statement with the Sum?


I have a report with two sub reports, the main report show the “Mother Batch” and details. And the Sub reports, show totals that were ship to customers.

The first Sub report shows jobs that were dyed and then shipped.
Where as the second report shows jobs that were sent with out being dyed (we’ll call them “raw” batches).

So from a Mother Batch, you can have all dyed or all Raw or a combination of both.

So far, so good. At the bottom of each sub report it Sum’s the total amount shipped.
Then it use this code to sum up both raw and dyed job’s in an unbound field.


And again this works fine. As long as its a combination of dyed and raw.

But if a job is totally Raw or totally Dyed, in other words there is only data returned in one sub report and the other is empty, the “Grand Total” returns an error. #Error

What can I do with the code/field to return a total in the Grand Total even if there is data in only one of the sub reports?

Perhaps an Iif Statement to check for null values?

I’m not that good with Iif yet, but still trying.

Any help would be great.


I have several sums I need to add together to get a total sum of hours in a text box. Only thing is that some of the sums are null(0)and when that happens i only get a blank in total hours.

=Sum([reg hrs])+Sum([ANN)])+Sum([SICK])+Sum([OTHER LEAVE])+Sum([OVER TIME]

What do I need to do..

Thank you

I am working on a report that summarizes sums of sales pro category, then a Total of these sums is calculated. The problem is that, if there are no sales in a category, the resulting sum is =0. The Total (of all sums) is then also =0.

So how can I calculate a Total that tolerates sums=0? Do I have to modify the Record Sources of the sales category sums?

I know one of you out there can help me with this. I posted yesterday, but hoping to find some help pretty fast.

Here's the deal. I have used this expression in another database and it works. But it won't in this one. The only thing I can think of, is the other database was created using Access 97 and I'm now using 2000. Heck, that might not be it, but it satisfies me so far. Anyway, what I'm trying to do is look up in a field and get the total contract price for all records that have no closing date. Maybe my formula will help to understand

=Abs(Sum([Contract Price]*(IsNull([Date Closed]))

Don't ask me how I finally figured it out for the existing database but I did, and it works perfectly.

What am I doing wrong?? I appreciate any help.


Hello Everyone

On a report i print the address from sql query
the format is

address line1
address line2
address line3
address line4
address line5

as address sometimes dont have all lines, sum fields contain no values so on report it puts a blank line, is there a way i can not put the field if it has no value???


I have a Credit and Debits report that shows an account’s transaction history. It is grouped by currency (e.g., US Dollar, Japanese Yen, Swiss Franc, etc…..).

In the each currency’s category header is a subreport with a Starting Balance field. It is calculated by a query which sums all the account’s transactions less than or equal to a start date, which the user inputs on a form – for each currency type.

In most cases, the starting balance will be a carryover value from the previous year. I have achieved this, no problem.

The problem is that if there are no transactions from the previous years and the currency account becomes active during the year (i.e. there will be transactions in the credit and debit history) the user wants the starting balance to begin at zero as of the start date. But since no records exist as of this date, the query is empty and thus the subreport is also empty.

Specifically, my problem is I need to conditionally value these nulls as zero.

Here is an example, assuming a Start Date of 1/1/2000:

An account holds US Dollars during 1999 and into 2000. The starting balance query calculates they have a starting balance of $10,000 on 1/1/2000. I can do this.

The account begins holding Japansese Yen on 1/31/2000 for the first time. How do I show a zero as of 1/1/2000? When in reality, no records exist i.e., it is null rather than zero.

And the account never has any CHF – so no Swiss currency group should be displayed i.e. these nulls should remain nulls.

I have a 'main' report comprising a number of sub-reports.

I need to display a single label on the 'main report' showing a cost value. The cost is calculated by summing the totals of each sub-report.

I can access the individual sub-report totals easily enough BUT the problem I am having is that if one of these sub-reports contains no data (which is a valid situation), the control source formula displays '#Error' becuase the subtotal is unavailable....

I am using a text field with the controlsource set to sum each subreport subtotal figure, how do I interrogate the subreport to only sum the sureport totals that exist?

NB. I have tried checking the sub-report being equal to Null (returns False)
every time....

I have also tried :
IsNull([Reporting_ProductionAnalysis_Materials subreport].Report![txtMatsTotal]) - also returns false


Not finding an answer? Try a Google search.