Addition With 0's

[jo15765]

I have been working all afternoon to try to get my query to return a 0 when there is no results to return, I have used the NZ function as well as the IIF function but nothing is causing the query to return 0 when it is 0. Can someone help!

IIF([tableStore1Info].[NumSold] IS NULL,0,[tableStore1Info].[NumSold])+IIF([tableStore2Info].[NumSold] IS NULL,0,[tableStore2Info].[NumSold])
NZ([tableStore1Info].[NumSold],0) + NZ([tableStore2Info].[NumSold],0)

[June7]

IS NULL is not correct in expression, use IsNull([fieldname])

Shouldn't this be enough:

Nz([tableStore1Info].[NumSold],0) + Nz([tableStore2Info].[NumSold],0)

What do you mean by 'no results' to return? If no records meet criteria then nothing will show no matter how you calculate that expression.

[jo15765]

I will test the NZ code you posted above when i can get back to my PC.

I meant 'no results' as I am running several of these calculations in one query, and I wasn't sure if it was one result not returning any resultscausing the entire query to bomb out.
Like NumSold is the above code, well I may have bonusSold which the code would be
Nz([tableStore1Info].[bSold],0) + Nz([tableStore2Info].[bSold],0)

So NumSold for example could return 10 but bonus sold could return 0. SInce I am running them in the same query, could one calculation returning 0 cause the entire query to not return any results?

[June7]

No, the calculations will not cause that. Filtering can cause query not to return results if no records meet criteria.

[jo15765]

Hmm...then I will give the NZ() code you posted below a twirl and see if that is what is causing the calculation to not display properly.

[jo15765]

I am still getting the same issue even with the NZ function. I am attaching a sample db with 1 query showing the issue of incorrect calculations, and 2 tables, that the calculation is performed on.

[June7]

No records show for two reasons:

1. there is no link (JOIN clause) between the two tables

2. tbl1 has no records

Why do you have two tables with identical structure?

[jo15765]

What field would I join on --- I may be missing it but I don't see the relationship between the data to create a join in this case.

The two tables have identical structures as they are from 2 separate locations and we haven't yet merged databases into one single table.

[June7]

Could be what you really need is UNION.

Review https://www.accessforums.net/queries...ort-36311.html