DSum not calculating if blank

[nicksource]

I have a DSum that does a simple subtraction.

As an example:
('FieldA' - 'FieldB') where FieldC = '123'

If FieldB is blank, no data there, but there is for FieldA, it doesn't show any result, it is blank.

Surely if FieldA = 300, then FieldB is blank, the result should be 300? As It's basically 300 subtract 0.

Any work around for this?

 

[boblarson]

Use the Nz function to deal with nulls:

encapsulate the fields -

Nz([FieldA],0) - Nz([FieldB],0)

 

[MSAccessRookie]

Try the Nz Function

I have a DSum that does a simple subtraction.

As an example:
('FieldA' - 'FieldB') where FieldC = '123'

If FieldB is blank, no data there, but there is for FieldA, it doesn't show any result, it is blank.

Surely if FieldA = 300, then FieldB is blank, the result should be 300? As It's basically 300 subtract 0.

Any work around for this?

You can use the Nz Function to change the Nulls to Zeroes. I think that this will give you what you are looking for. The syntax would look like this:

FieldC=(Nz(FieldA) - Nz(FieldB))

 

[nicksource]

Thanks guys, I don't think that worked though, fields are still appearing blank where a figure should be, here's my Dsum:

=DSum(Nz('[Qty]',0),'GoodsIn','[GoodsIn]![ProdID]=[IDa]')-DSum(Nz('[Qty]',0),'GoodsOut','[GoodsOut]![ProdID]=[IDa]')

 

[boblarson]

Change it to this:
=DSum(Nz('[Qty]',0),'GoodsIn','[ProdID]=[IDa]')-DSum(Nz('[Qty]',0),'GoodsOut','[ProdID]=[IDa]')

You don't put the table name in the criteria. You already have the table name specified as the middle parameter.

 

[nicksource]

Ah thanks for the tip, although it still doesn't work.

It's fine if a number is there but if I leave it blank the formula just doesn't show anything.

Thanks Bob,
Nick.

 

[boblarson]

How about this

=Nz(DSum('[Qty]','GoodsIn','[ProdID]=[IDa]')-DSum('[Qty]','GoodsOut','[ProdID]=[IDa]'),0)

 

[nicksource]

Thanks a lot Bob much appreciated, I've used that but altered it a little to this (so if one field is blank it doesn't revert it all to 0):

=Nz(DSum('[Qty]','GoodsIn','[ProdID]=[IDa]'),0)-Nz(DSum('[Qty]','GoodsOut','[ProdID]=[IDa]'),0)


Works a treat now.

 

[boblarson]

Cool - glad you finally got it.