ACCESS: Query Formula

[gheyman]

Using the Builder, I entered a new field in my query titled TOTAL

TOTAL: [START_UP_HOURS]+[HOURS_PER_MONTH]

Very simple formula, but it doesn't work. Any ideas Why??

I do have values in some of my rows for these two fields. But my Totals column is blank when I run my query.


Follow-up
I see whats happenening, it needs a value in both fields. So if Start up is 5 and Hours Per is blank the formula doesn't result in 5. Is there a way to get a value in my formula field even if one of the fields in the formula is blank?

Do I have to default all the fields to 0?

 

[Joe4]

Use the NZ function, which indicates what to use in placed of NULL values. Then all nulls in those fields will be treated like zeroes.

[COLOR=#333333]TOTAL: NZ([START_UP_HOURS],0)+NZ([HOURS_PER_MONTH],0)
[/COLOR]

 

[Micron]

FYI for future - calculations involving Null result in Null. In your case, it must be that every record contains Null in one or both fields. It could be as simple as what you've encountered, or buried deep in an expression. Something to consider going forward whenever doing calculations.