total in a form

[aman909]

Hi,

I would like to calculate the total on a form. But the problem is that i need it to calculate from 5 text boxes on a form, but not all this text boxes are filled out depending on a client.

E.g. one client will have 3 out of the 5 text boxes filled out while another will only have 1 out the 5. So i need to be able to work out the total, but the text boxes that aint filled out have to stay empty it cant contain a 0, zero.

what is the best way to be able to do this?

[missinglinq]

You can use the Nz function to return a zero for a null value.

NullValue + 1 = Null

but

Nz(NullValue) + 1 = l

This works fine if you're adding values together, because you want an empty textbox to evealuate to 0 in your calculations. But what if you want to multiply?

10 * Nz(NullValue) = 0, because 10 x 0 = 0.

While zero is the default that Nz returns for a null or empty string, you can also set it to represent another value, using Nz(NullValue, 1).

In this case 10 * Nz(NullValue, 1) = 10, because 10 x 1 = 10.

The same would be true if you were dividing.

10 / Nz(NullValue) = 0, because, strange as it sounds, 10 / 0 = 0!

And in Access, it's illegal to divide by 0, so runtime error 11 would be thrown. Since we ususally expect that a number when not divided by anything equals itself, we can again use Nz(NullValue, 1) so that

10 / Nz(NullValue, 1) = 10 because 10/1 = 10.

You could also use Nz to return a string value. In a report, for instance, you might be reporting an agent's sales for a month, and rather than showing a blank space or a zero if he had no sales, you want it to say "No Sales" you could use

Nz(MonthlySales, "No Sales")

and on the report it would printout as "No Sales"

Good Luck!

Linq ;0)>