DSUM Encounters Null Values

[CementCarver]

When clients update the square footage of their buildings, I want to maintain a running total display. But there are some buildings that they don't have, so the square footage is null.



So when I try to sum all building types with the line below, I get an error "Invalid use of null"

building_sq_ft = DSum("[ArenaSqFt] + [ShedSqFt] + [MaintSqFt]", "ALL_BUILDINGS", "MEMBER_ID=" & [current_id])

How does one deal with a null value when it's encountered in the dsum function? So, if the above line for MaintSqFt, it's value is null.

Any help would be appreciated....CementCarver

[Missinglinq]

Nulls are usually handled with the Nz() Function to assign an appropriate value when a Field is Null:

Nz([ArenaSqFt],0)

If [ArenaSqFt] is Null, it gets assigned 0 value.

You could replace the 0 with any DataType appropriate Value.

Linq ;0)>

[CementCarver]

Thanks for the super quick reply. I'll give it a try and will close the call if successful.

Thx...CementCarver

[CementCarver]

Not sure of the syntax....

building_sq_ft = DSum("Nz([ArenaSqFt],0) + Nz([ShedSqFt],0) + Nz([MaintSqFt],0)", "ALL_BUILDINGS", "MEMBER_ID=" & [current_id])

Is this the correct syntax?

[CementCarver]

No luck still even with below:

building_sq_ft = Nz(DSum("[ArenaSqFt] + [ShedSqFt] + [MaintSqFt]", "FULL_COMM_BREAKDOWN", "MEMBER_ID=" & [current_id]), 0)

this is yielding a building_sq_ft of zero...!!!

Is this line syntax correct?