Cannot subtract column A from column B 2

[Tony220]

My report is based on a query. One column in the report, "Prior years", is the result of subtracting two columns in the query
If there is data in both columns, it works.
If, however, one column does not have data, the text box remains empty. It should however give me the result, making believe that the empty column has a zero.

Example:
Row A B current result Desired result
1 0
2 9 4 5 5
3 9 9
4 9 (9)

Background:
The query is a sum query for a "group by" records of a given type.
In some situations there are no records for that type so the sum remains empty.

Thanks

 

[melaniecarr23]

Can you use an iif statement to make a 0 show if the result is less than 1?

 

[Lilliabeth]

It should however give me the result, making believe that the empty column has a zero." - No, it shouldn't. Access does not treat nulls as if they were 0s.

Since you plan to do math with these fields, the Required properties should be set to Yes and the Default Value properties should be set to 0.

If you already have a lot of records where you have nulls when you should have 0s, you can use an update query to change them.

 

[Tony220]

Thank you very much Lilliabeth, The problem now makes sense.
The question is how to solve it!

The source of these null’s is an outer join query. (all records from both tables)
The query groups by “a set” and sums all the records belonging to that set for each table.
In some cases there are no records for that set on one of the tables.
There is actually a table that defines the possible “sets” so it could be that neither table has any records to sum for that set, but the query will still show a record with that set and no sums for it.

How do I go about making sure that the default value - if there are no records - will be zero?

If I am not clear, please tell me and I will try to give an example.
Thanks again

 

[Lilliabeth]

Make calculated fields:

NewColA:IIF([ColA] is null,0,[ColA])

NewColB:IIF(ColB] is null,0,[ColB])

Prior Years:[NewColA]-[NewColB])

 

[Tony220]

Great it works - thank you
one last question if I may, I now want to exclude some fields where everything is zero.
I tried as criteria [prior in]<>'0'
and planned using some or's but I get a type mismatch error.
the [prior in] is a currency field.
any ideas,
Thanks a load

 

[Lilliabeth]

Don't put apostrophes around the zero.

 

[Tony220]

as soon as I tab or enter out of the feild in the query design view, access adds double quotes to it, unless I add single quotes.
This I believe will make it text, which would justify the type mismatch,

 

[JerryKlmns]

Another way:

Prior Years : Nz([ColA])-Nz([ColA])
Criteria <> 0

Then, in sql view change the Where to Having

Yes, double quotes and single quotes make the field to be treated as text, not numeric.