Text box has no value but is not null?

[ARW17]

I have an equation on a subform to add up the number of vacation days scheduled. The equation is
=Sum([Duration by Day])

It works if the person has a vacation day scheduled, but if they don't I'm getting #Error.

I tried to fix it by writing =iif(isnull(Sum([Duration by Day]))=true, 0, Sum([Duration by Day]), but this still results in an error.

How can this text box have no value, but not be null?

Please help me fix this error

 

[Joe4]

Applying IsNull to the SUM is too late, because the SUM is an error at that point, not null. It is some of the individual items that might be Null.

Try this:

[COLOR=#333333]=Sum(Nz([Duration by Day],0))[/COLOR]

You use the Nz function to tell Access how to handle the Nulls. We are telling it to treat them as zeroes.
See here for help on the Nz function: https://www.techonthenet.com/access/functions/advanced/nz.php

 

[ARW17]

Applying IsNull to the SUM is too late, because the SUM is an error at that point, not null. It is some of the individual items that might be Null.

Oh, I get what you're saying, but I changed it to your suggestion and am still getting the #Error.

 

[Joe4]

Where exactly are you placing this formula on the SubForm?
In which section?

Can you post some small data samples of what is trying to be included in this SUM?
(I am curious as to the structure of your data and SubForm and how many records may be included in your SUM).

 

[ARW17]

Where exactly are you placing this formula on the SubForm?
In which section?

In the form footer.

Employee	UserID	EventDate	EventTypeCode	Duration by Day
Falvo, Amanda	T3ARF	01/30/17	V	1
Falvo, Amanda	T3ARF	02/05/17	V	0.5

<caption> VacDaySch </caption><thead>
</thead><tbody>
</tbody><tfoot></tfoot>

 

[Joe4]

So, is the issue when an employee has no records, or has a record where the Duration by Day field is Null, or both?
Is the Duration by Day a calculated field also?
If so, what is the formula that calculates it?

 

[ARW17]

If the employee has no vacation days scheduled, there will be no records.
The Duration by Day field is not calculate, it's just a number in a table.

 

[Joe4]

So, in post #5, you should a nice picture of a sample of the information in your Subform.
Can you post some examples of what this looks like when you get that error?
I am just trying to determine if the situation causing the error is when there are no records, or there is a record, but just nothing in that field?

 

[ARW17]

Yes, it will be blank:

Employee	UserID	EventDate	EventTypeCode	Duration by Day

<caption> PersDaySch </caption><thead>
</thead><tbody></tbody><tfoot></tfoot>

 

[Joe4]

OK, so the issue is that there is no data present on the Subform to sum.
I would probably just try to hide the entire Subform in that instance then.
See: Forms: Hide a subform if no records present