I want zeroes instead of blanks

[chancer1967]

I have a database for credit collection. The main table is a list of invoices - invoice number, value, date, etc

From that I produce an aging report, which for each customer gives a balance (sum of invoices), and then 5 aging columns (0-30 days, 31-60, etc)

To do this I have 5 queries, one for each aging band, and then a final query which collates the results into a summary table.

In am not sure whether this is an efficient way of doing it or not, but anyway...

At a later point I have a query that adds the aging data to other data and my problem is this:

the aging columns in my summary table has a lot of blank cells. For example, if a customer owed $100 and it was all in the aging band 31-60 days, the 5 aging columns would show: blank, $100, blank, blank, blank

when my later query adds these to other figures, it seems to decide that a blank + a number = a blank.


what I need is either:
- a way of making access treat a blank as a zero, so that blank + number = number; or
- an easy way of replacing blanks with zeros

I have already tried the second approach but discovered I need a separate update query for each field - so i need 5 queries just to fix the summary table. its all a bit messy.

Hopefully you see what I am after.


Thanks in advance

 

[Mike375]

A simple way is to make another field in your queries based on the field that has a lot of blanks and using the IIF function.

NewField:IIF([FieldNamewithsome blanks] Is Null,0,([FieldNamewithsome blanks])

So where your field has blanks the new field will a 0 entered and where it is not blank the actual number in that field will appear in the new field.

Mike

 

[___]

Set the default value of the field in your table to 0.

 

[namliam]

In am not sure whether this is an efficient way of doing it or not, but anyway...

It most definatly is not, try looking into a crosstab query. This can do all that and total it (see later) in 1! Let me know if you need more help.

blank + a number = a blank.

Yes well thats the way databases work, Null (real name of your blank) + something = Null
It works that way with both numbers AND strings. Now to resolve it try using NZ([YourField],0), this replaces a Null by 0 allowing you to add.... But really try the Crosstab, you will like it.

Regards