Join Types and inputting zeroes

[athyeh]

Hi Folks,

Using the relationship manager I have created several join types that include all of the values from Query1, Field A, and ONLY those records from Query2, Field B, where the joined fields are equal.

When I run the query, everything works great, and the fields populate where there is a match.



However, when there is no match instead of inputting a "0" or something else, the cell in Field B just remains blank. Which creates problems when I'm trying to create the calculated field in a new textbox:

=IIf([SumofA]="","100%",1-([B]/[A]))


Because Field B is empty it doens't pick up and in my new calculated field there is just a blank space. Can someone help with this?

[Dal Jeanis]

Use the NZ function to replace B with an appropriate value.

=IIf([SumofA]="","100%",1-(NZ([B],0)/[A]))

[athyeh]

Thank you.

[athyeh]

Can you refer me to a beginner's guide to the NZ function? Keeps on popping up..

[Dal Jeanis]

This will automatically replace any Null values with a zero or empty string, whichever seems appropriate to the Jet database engine.

NZ(MyTable.MyField)

This will automatically replace any Null values with an expllicit default value of "XYZ".

NZ(MyTable.MyField,"XYZ")

For a text field, this can achieve the same thing

MyTable.MyField & ""

Here's a couple references I found by googling "MS Access 2010 NZ function".
http://office.microsoft.com/en-us/ac...001228890.aspx
http://msdn.microsoft.com/en-us/libr.../ff195223.aspx