Darn Null Problems

[pcdummy]

OK I have tried every syntax i can think of and am still receiving an #Error with a null value... Please help


Ok I have a table (tblAdjustments)

AdjustmentID
Description
AdjustmentCost

from this is created a subform.. in the footer have a calculated text box

txtTotalAdjustments
Control Source =Sum([AdjustmentCost])

This works great.. if a parent has adjustments with values.

However, am trying to call the Calculated Text Box into my main form... again works fine with values.. win a record returns zero adjustments. comes back with error..

AM i clear enough.. what am I doing wrong.. please help..

Thanks
jeff

 

[The Real Yoda]

Look Up The IsNull facility:

This example uses the IsNull function to determine if a variable contains a Null.

Dim MyVar, MyCheck
MyCheck = IsNull(MyVar) ' Returns False.

MyVar = ""
MyCheck = IsNull(MyVar) ' Returns False.

MyVar = Null
MyCheck = IsNull(MyVar) ' Returns True.

[This message has been edited by The Real Yoda (edited 06-26-2001).]

 

[MSUKevin]

Try this to get rid of your null values:

When you can the value up to the main form use an IIF Null statement:

=IIf([subform_name].[Form]![subform_control] Is Null,0,[subform_name].[Form]![subform_control]

You can use this to either display the value or use it to preform further calculations.

You could also try writing your expression with the nz funtion:

=Sum(nz[AdjustmentCost])

Although I'm not that familiar with the nz funtion.

Try looking in Access help, search the index for Null values

HTH
Kevin

 

[jimbrooking]

This used to be a major problem with me, too, until I discovered the Nz function. Its syntax is Nz(TestValue,ValueIfNull).

If the TestValue is Null, then the ValueIfNull is returned. Otherwise the TestValue is returned. So

Nz(Null,"N/A") returns N/A

Nz(Null,0) returns 0

Nz(10,0) returns 10

Saves lots of logic and headaches.

Jim

 

[Pat Hartman]

Jim, be careful if you substitute a string rather than 0 when using the Nz() function. This causes Access to return a string rather than a number even when the result is numeric.