Setting blanks with Zeros

[jennilein]

If I have a query, with let's say 4 tables (A,B,C,D). It's a one to many relationship from A to the others.

I want to calculate different measures between fields pulled from all tables, however if table B doesn't have a value for a particular record - it leaves the field blank - so I can't make a certain calculation. Is there anyway to make the query see the blanks as a zero, so I can do my calculations?

I understand that I could make a table and set default values to zero - but is there anyway that this can be done in the query??

 

[edsuk]

You can use the IIf function, which returns a value based on the result of the expression, example below:

Expr1: IIf([TableB]![ColumnX] Is Null,0,[TableB]![ColumnX])

If no value exists for ColumnX then 0 is returned.

Hope This Helps.

Mark...

[Worm]

 

[AlexMidd]

How about

IIf(isNull([Field]), 0, [Field])

?


Have fun! )

Alex Middleton

 

[AlexMidd]

Oops, mark beat me to it.

Have fun! )

Alex Middleton

 

[PHV]

Have a look at the Nz function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886