math calculation problem

[johnmaio]

am calling a field in a query and have to include all the records even if some are columns are blank...

the problem is when the column is blank i can not do a matematical expession

how do i do a math calculation on a blank field...i just want the blank to have the value of zero then if one field is 52 - Blank it will show it as 52 instead of showing it as null

 

[TonyJollans]

Hi johnmaio,

What you need really depends on whether you have Nulls or Blanks. The general format is ..

[Field52] - Iif([OtherField]="",0,[OtherField])

.. or ..

[Field52] - Iif(isNull([OtherField]),0,[OtherField])

If the values are really null there is a short form using the NZ function ..

[Field52] - Nz([OtherField],0)

Enjoy,
Tony

 

[johnmaio]

tony,

your a god...thanks!

 

[MichaelRed]

If you are 'summing' a COLUMN (field) in a query, the aggregate function will ignore both blanks and nulls.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places