calculating null values

  • Thread starter Thread starter Carlos
  • Start date Start date
C

Carlos

I have 3 different values a record can have.
Satisfactory, unsatisfactory and Needs Improvement. If
Satisfactory comes up 80% of the time then I have to
display Satisfactory as a final rating for an employee.
The other two have calculations around them to and
everything I want to work works exactly how I want it if
all ratings are choosed at least once. But it is possible
that one of these ratings never gets choosen. If this
happens my calculation doesnt work because it doesnt
recognize null.

Can I somehow tell the field to return 0 if it is null.
If so would I put this formula on the first query that
counts the items or the final one that checks what % each
rating has.

Help Please!!
 
I believe you're looking for the NZ function.

Nz(variant[, valueifnull])

Help info: You can use the Nz function to return zero, a
zero-length string (" "), or another specified value when
a Variant is Null. For example, you can use this function
to convert a Null value to another value and prevent it
from propagating through an expression.
 
I cant get it working. Ok the name of my field is
RatingDescription. I want to count how many times
Satisfactory comes up but in this case there are none.

From what I understand from below I would write it like
this

expr1 : NZ(RatingDescription[ , 0])

Is that correct?

-----Original Message-----
I believe you're looking for the NZ function.

Nz(variant[, valueifnull])

Help info: You can use the Nz function to return zero, a
zero-length string (" "), or another specified value when
a Variant is Null. For example, you can use this function
to convert a Null value to another value and prevent it
from propagating through an expression.
-----Original Message-----
I have 3 different values a record can have.
Satisfactory, unsatisfactory and Needs Improvement. If
Satisfactory comes up 80% of the time then I have to
display Satisfactory as a final rating for an employee.
The other two have calculations around them to and
everything I want to work works exactly how I want it if
all ratings are choosed at least once. But it is possible
that one of these ratings never gets choosen. If this
happens my calculation doesnt work because it doesnt
recognize null.

Can I somehow tell the field to return 0 if it is null.
If so would I put this formula on the first query that
counts the items or the final one that checks what % each
rating has.

Help Please!!
.
Click to expand...
.
Click to expand...
 
No, the brackets are syntax the indicates that the value
in the brackets are optional. Do it like this:
expr1 : NZ(RatingDescription)

If you want it to be something other then zero though, say
10, It would be like this;

expr1 : NZ(RatingDescription,10)



-----Original Message-----
I cant get it working. Ok the name of my field is
RatingDescription. I want to count how many times
Satisfactory comes up but in this case there are none.

From what I understand from below I would write it like
this

expr1 : NZ(RatingDescription[ , 0])

Is that correct?

-----Original Message-----
I believe you're looking for the NZ function.

Nz(variant[, valueifnull])

Help info: You can use the Nz function to return zero, a
zero-length string (" "), or another specified value when
a Variant is Null. For example, you can use this function
to convert a Null value to another value and prevent it
from propagating through an expression.
-----Original Message-----
I have 3 different values a record can have.
Satisfactory, unsatisfactory and Needs Improvement. If
Satisfactory comes up 80% of the time then I have to
display Satisfactory as a final rating for an employee.
The other two have calculations around them to and
everything I want to work works exactly how I want it if
all ratings are choosed at least once. But it is possible
that one of these ratings never gets choosen. If this
happens my calculation doesnt work because it doesnt
recognize null.

Can I somehow tell the field to return 0 if it is null.
If so would I put this formula on the first query that
counts the items or the final one that checks what % each
rating has.

Help Please!!
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top