Null values in calculations.

[dwcrockford]

OK.... there's a process to this one and various objects.

tblHours - has days of the week (numeric) default value set to "0"
tbhHours subform - used in another form, based from the above table
frmTimesheet - main user form which has the above subform on it as well as additional calculated form objects.

On the main user form (frmTimesheet), some days will not have any hours entered, so they are left at "0" by default (at least this is what is displayed.

Within the form, there are calculations based on the tblHours subform. All the calculations work just fine - IF there are numbers entered or you manually enter "0". If a entry is skipped on the subform, the subsequent calculations leave those calculation object empty (no "0" - just a blank box).

This affects further calculations as well leaving more blank form objects.

I know somewhere, a "null" statement needs to be entered (isnull=0 or similar code that is correct), but where along the way should this statement be entered - somewhere on the source table (Hours), subform or main user form (and what is the correct code to use?)

D

 

[Micron]

Not sure if I completely get the drift, but it sounds like the default 0 is set at the table level. If you do not create a record, the default is not saved as a record.

If a entry is skipped on the subform

That's not how it works. Only when you create a record by entering data in other fields but don't supply a value for those with defaults do the defaults kick in.

isnull=0

If this is a variable, it's a name you should not use. If it's an expression you created, it will never be true.

I can't tell you exactly where to put an expression without knowing what's behind the form/subform and the relationships, but to save a 0, your code or calculated control (unbound, because bound controls cannot contain expressions) can use the IIF expression IIF(IsNull(something),0,Null) or the NZ function NZ(something,0). If nulls are not allowed in the table, you can use empty strings "" as long as the data type is string NZ(something,"")