Calculations with "Empty" values

[Carlos]

Hello everyone and thank you for all your support,

I am having a little problem that should have a reasonably easy solution. I
have to make some addition calculations in one of my queries.. where a list
of costs should be added up to give me a grad total. The problem is that
sometimes, some of these costs are not present. I am having a hard time
trying to get Access to understand these "empty" values as zeros for the
calculation.

But that's not it.. I dont want to set the default value for these costs as
zero, cause I don't want a list of meaningless zeros on the report I
generate. I want the have the boxes there and transparent, so that when there
are no values, nothing shows up on the report.

Basicaly, I need to find a way to consider all "empty" values as zeros for
the calculation. Maybe with a AutoSum function or something..

I'd appreciate any help or tips,
Carlos

 

[Joshua A. Booker]

Carlos,

I would still set the default value to zero. You can simply restict zeros
from printing on the report.

Try the Nz() function. This converts a null value to empty string or some
other value you specify.

i.e. Nz([FieldName],0)

If you have empty strings instead of nulls this won't work. In that case
you can write a function that compares to empty and set to zero.

HTH,
Josh

 

[Carlos]

If I do set the default value to zero, how do I restrict the zeros from
showing up on my report?
I am currently using transparent boxes, that when empty or null don't show
up.
I have:

Total: finance![Cost 1]+finance![Cost 2]+finance![Cost 3]+finance![Cost 4]

Where finance is my table and Cost 1,2,3,4 are the costs to be added up.
Problem is, I only get a value for "Total" when I have values for all Costs
1,2,3,4.

I want it to add up even if some of these costs have null values.

Thank you for everything,
Carlos

"Joshua A. Booker" escreveu:

Carlos,

I would still set the default value to zero. You can simply restict zeros
from printing on the report.

Try the Nz() function. This converts a null value to empty string or some
other value you specify.

i.e. Nz([FieldName],0)

If you have empty strings instead of nulls this won't work. In that case
you can write a function that compares to empty and set to zero.

HTH,
Josh

Hello everyone and thank you for all your support,

I am having a little problem that should have a reasonably easy solution. I
have to make some addition calculations in one of my queries.. where a list
of costs should be added up to give me a grad total. The problem is that
sometimes, some of these costs are not present. I am having a hard time
trying to get Access to understand these "empty" values as zeros for the
calculation.

But that's not it.. I dont want to set the default value for these costs as
zero, cause I don't want a list of meaningless zeros on the report I
generate. I want the have the boxes there and transparent, so that when there
are no values, nothing shows up on the report.

Basicaly, I need to find a way to consider all "empty" values as zeros for
the calculation. Maybe with a AutoSum function or something..

I'd appreciate any help or tips,
Carlos

 

[Joshua A. Booker]

Carlos,

Try this:

Total: nz(finance![Cost 1],0)+nz(finance![Cost 2],0)+nz(finance![Cost
3],0)+nz(finance![Cost 4],0)

You can restrict zeros in the query for the report's recordsource.

HTH,
Josh

If I do set the default value to zero, how do I restrict the zeros from
showing up on my report?
I am currently using transparent boxes, that when empty or null don't show
up.
I have:

Total: finance![Cost 1]+finance![Cost 2]+finance![Cost 3]+finance![Cost 4]

Where finance is my table and Cost 1,2,3,4 are the costs to be added up.
Problem is, I only get a value for "Total" when I have values for all Costs
1,2,3,4.

I want it to add up even if some of these costs have null values.

Thank you for everything,
Carlos

"Joshua A. Booker" escreveu:

Carlos,

I would still set the default value to zero. You can simply restict zeros
from printing on the report.

Try the Nz() function. This converts a null value to empty string or some
other value you specify.

i.e. Nz([FieldName],0)

If you have empty strings instead of nulls this won't work. In that case
you can write a function that compares to empty and set to zero.

HTH,
Josh

Hello everyone and thank you for all your support,

I am having a little problem that should have a reasonably easy

solution.
I

have to make some addition calculations in one of my queries.. where a list
of costs should be added up to give me a grad total. The problem is that
sometimes, some of these costs are not present. I am having a hard time
trying to get Access to understand these "empty" values as zeros for the
calculation.

But that's not it.. I dont want to set the default value for these

costs
as

zero, cause I don't want a list of meaningless zeros on the report I
generate. I want the have the boxes there and transparent, so that

when
there

are no values, nothing shows up on the report.

Basicaly, I need to find a way to consider all "empty" values as zeros for
the calculation. Maybe with a AutoSum function or something..

I'd appreciate any help or tips,
Carlos

 

[Carlos]

Yes! Thank you very much Josh.
Your information helped me alot.
Only thing is, the ",0" is incorrect. The nz fuction goes back to zero
automatically. I tried it as u said and got an error on the commas.. I then
looked into the nz function and saw the correct format to it.

But this was exactly what I wanted. Thank you very much.

"Joshua A. Booker" escreveu:

Carlos,

Try this:

Total: nz(finance![Cost 1],0)+nz(finance![Cost 2],0)+nz(finance![Cost
3],0)+nz(finance![Cost 4],0)

You can restrict zeros in the query for the report's recordsource.

HTH,
Josh

If I do set the default value to zero, how do I restrict the zeros from
showing up on my report?
I am currently using transparent boxes, that when empty or null don't show
up.
I have:

Total: finance![Cost 1]+finance![Cost 2]+finance![Cost 3]+finance![Cost 4]

Where finance is my table and Cost 1,2,3,4 are the costs to be added up.
Problem is, I only get a value for "Total" when I have values for all Costs
1,2,3,4.

I want it to add up even if some of these costs have null values.

Thank you for everything,
Carlos

"Joshua A. Booker" escreveu:

Carlos,

I would still set the default value to zero. You can simply restict zeros
from printing on the report.

Try the Nz() function. This converts a null value to empty string or some
other value you specify.

i.e. Nz([FieldName],0)

If you have empty strings instead of nulls this won't work. In that case
you can write a function that compares to empty and set to zero.

HTH,
Josh

Hello everyone and thank you for all your support,

I am having a little problem that should have a reasonably easy solution.
I
have to make some addition calculations in one of my queries.. where a
list
of costs should be added up to give me a grad total. The problem is that
sometimes, some of these costs are not present. I am having a hard time
trying to get Access to understand these "empty" values as zeros for the
calculation.

But that's not it.. I dont want to set the default value for these costs
as
zero, cause I don't want a list of meaningless zeros on the report I
generate. I want the have the boxes there and transparent, so that when
there
are no values, nothing shows up on the report.

Basicaly, I need to find a way to consider all "empty" values as zeros for
the calculation. Maybe with a AutoSum function or something..

I'd appreciate any help or tips,
Carlos

 

[Joshua A. Booker]

Carlos,

I'm glad you got it to work.

For the record, the nz function does not go to zero auotomatically.
It goes to a zero length string. For example:

nz(Null)= ""
nz(Null,0)=0

HTH,
Josh

Yes! Thank you very much Josh.
Your information helped me alot.
Only thing is, the ",0" is incorrect. The nz fuction goes back to zero
automatically. I tried it as u said and got an error on the commas.. I then
looked into the nz function and saw the correct format to it.

But this was exactly what I wanted. Thank you very much.

"Joshua A. Booker" escreveu:

Carlos,

Try this:

Total: nz(finance![Cost 1],0)+nz(finance![Cost 2],0)+nz(finance![Cost
3],0)+nz(finance![Cost 4],0)

You can restrict zeros in the query for the report's recordsource.

HTH,
Josh

If I do set the default value to zero, how do I restrict the zeros from
showing up on my report?
I am currently using transparent boxes, that when empty or null don't show
up.
I have:

Total: finance![Cost 1]+finance![Cost 2]+finance![Cost 3]+finance![Cost 4]

Where finance is my table and Cost 1,2,3,4 are the costs to be added up.
Problem is, I only get a value for "Total" when I have values for all Costs
1,2,3,4.

I want it to add up even if some of these costs have null values.

Thank you for everything,
Carlos

"Joshua A. Booker" escreveu:

Carlos,

I would still set the default value to zero. You can simply restict zeros
from printing on the report.

Try the Nz() function. This converts a null value to empty string

or
some

other value you specify.

i.e. Nz([FieldName],0)

If you have empty strings instead of nulls this won't work. In that case
you can write a function that compares to empty and set to zero.

HTH,
Josh

Hello everyone and thank you for all your support,

I am having a little problem that should have a reasonably easy solution.
I
have to make some addition calculations in one of my queries.. where a
list
of costs should be added up to give me a grad total. The problem

is
that

sometimes, some of these costs are not present. I am having a hard time
trying to get Access to understand these "empty" values as zeros

for
the

calculation.

But that's not it.. I dont want to set the default value for these costs
as
zero, cause I don't want a list of meaningless zeros on the report I
generate. I want the have the boxes there and transparent, so that when
there
are no values, nothing shows up on the report.

Basicaly, I need to find a way to consider all "empty" values as

zeros
for

the calculation. Maybe with a AutoSum function or something..

I'd appreciate any help or tips,
Carlos