totalling 3 tables, 1 is null on occassions

[Chimp8471]

i have a query that uses data from a series of tables, the trouble i have is that if one of the tables is null i don't get any data returned.

current query:

Dtime: [Mktbl_otherDTime]![Other]+[Mktbl_UnacDTime]![Total]+[mk_tblBreakdowns]![Breakdowns]

example:

if i have data in:-

[Mktbl_otherDTime]![Other]
[Mktbl_UnacDTime]![Total]

but no value in:-

[mk_tblBreakdowns]![Breakdowns]

i need the total of tables with data so effectivly the third table will be classed as zero

how should i do this please

Andy

 

[Jon K]

You can use the Nz() function to convert nulls to zeros:

Dtime: [Mktbl_otherDTime].[Other]+[Mktbl_UnacDTime].[Total]+Nz([mk_tblBreakdowns].[Breakdowns])
.

 

[stephen81]

Dtime: Mktbl_otherDTime].[Other]+[Mktbl_UnacDTime].[Total]+Nz([mk_tblBreakdowns].[Breakdowns])
.

I'm not 100% sure but I think as you're using this in a query you have to include the value if null in the Nz function.
i.e. Nz([mk_tblBreakdowns].[Breakdowns],0)

 

[Jon K]

I'm not 100% sure but I think as you're using this in a query you have to include the value if null in the Nz function.
i.e. Nz([mk_tblBreakdowns].[Breakdowns],0)

When Nz([mk_tblBreakdowns].[Breakdowns]) stands alone, you need the ,0. But the values returned become text.

When Nz([mk_tblBreakdowns].[Breakdowns]) is included in a calculation, you don't need the ,0
.