Prefill data ouput with a zero if blank

[Swifey]

Could you please help I’m using three Tables in my query as follows:-

Table1 – Contains the Product Codes I want to analyse
Table2 – Contains the Product Sales for period 1
Table3 – Contains the Product Sales for period 2

1 I link Table1 by product to Table2 to include ALL records from Table1.
2 Then I link Table1 by product to Table3 to include ALL records from Table1.

My output is as follow

Products(Table1) Period 1(Table2) Period 2(Table3)
1234 1 1
4321 1
5746 1
9946
8940 1

As you can see I get BLANKS coming through. - Is there any way in the query, that I can fill all the blanks with a zero?

Kind Regards
Lynda

 

[Corticus]

Replace your 2 product sales fields in your query with expressions such as follows:

FixBlank1: IIf(IsNull([ProductSales1])=True,0,[ProductSales1])
FixBlank2: IIf(IsNull([ProductSales2])=True,0,[ProductSales2])

HTH,

 

[Swifey]

Thank you for your help

Regards
Swifey

 

[Corticus]

I wanted to add that it would be better to set the default value of these two fields to zero; no calculations.

Corticus

 

[Swifey]

Thats worth knowing

Thank you
Lynda