Totals don't calculate correctly (1 Viewer)

Please bear with me here; I'm a novice when it comes to Access so any answers will need to be spelled out very sssssssllllllllllloooooooowwwwwwwwllllllllllyyyyyyyy.

I've put together a database fashioned after a template I located on the Microsoft templates - Time and Billing database. I'm having a bit of trouble getting all totals displaying on the subforms and will try to describe it.

The main form is for the clients with the subform displaying all the payroll jobs associated with that particular client. The subform lists the payroll job name, hours, fees, expenses plus a calculated field for the total billable amount. The hours and expenses come from the time cards which have two subforms; one for hours and one for the expenses.

What's happening is that if I enter hours for a payroll job and there are no associated expenses, the total billable amount on the clients subform doesn't display. It shows the hours and the fees for those hours but the total billable amount doesn't show. If I go back into the time card and then go to the expense tab and put zero expenses in, the total billable amount shows on the client form.

Not all jobs will have expenses and I find it at least inconvenient to have to enter something to get the total to display.

I've tried setting the default value to zero in the tables/form properties but nothing seems to work. I've also tried putting in an IIF statement to no avail. Any ideas would be greatly appreciated but please remember; I'm old and slow!

Thanks in advance.
cclark9589

You are suffering from Nulls in your fields. So, you can simply wrap the NZ function in your expressions for doing the calculations. For example:

=Nz([YourExpenseAmt],0) + Nz([BillableTimeField],0)

boblarson:

I'd be very careful with answers that quick that solve the problem! You're in Oregon and I'm in Washington so it's not too far away for me to come give you a big smack on the lips!

What I've spent a few hours trying to solve you answered in mere minutes and just as important, you went very slow with the answer.

One further question, if I may: what does "nz" mean or stand for?

cclark9589

I think NZ means "null to zero" or something like that. According to help:

You can use the Nz function to return zero, a zero-length string (zero-length string: A string that contains no characters. You can use a zero-length string to indicate that you know no value exists for a field. You enter a zero-length string by typing two double quotation marks with no space between them (" ").) (" "), or another specified value when a Variant (Variant data type: The default data type for variables that don't have type-declaration characters when a Deftype statement isn't in effect. A Variant can store numeric, string, date/time, Null, or Empty data.) is Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain Null.). For example, you can use this function to convert a Null value to another value and prevent it from propagating through an expression.

Click to expand...