Nz function doesn't work in simple query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am creating a simple query. The table is InsuranceExpense. The field is
InsAmt. There is nothing in the table yet. I put TtlInsurance:
Sum(CCur(Nz([InsAmt],0))) in the row marked field. I get only a blank field.
My goal is to use the zero in a calculation with another table.
 
If there's nothing in the table, then you get no row back, as opposed to a
row that has a Null in it.
 
Thank you Mr. Steele. That makes perfect sense to me. How then, can I use
the sum of the total of tblInsurance, which is 0 in a calculation?

Douglas J. Steele said:
If there's nothing in the table, then you get no row back, as opposed to a
row that has a Null in it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



KimClark said:
I am creating a simple query. The table is InsuranceExpense. The field is
InsAmt. There is nothing in the table yet. I put TtlInsurance:
Sum(CCur(Nz([InsAmt],0))) in the row marked field. I get only a blank
field.
My goal is to use the zero in a calculation with another table.
Click to expand...
Click to expand...
 
Depends on what you're trying to do.

If, for example, you've got a typical parent/child setup, so that each entry
in InsuranceExpense is related to an entry in another table (call it
ExpenseCategory, for sake of argument), you could use a Left Join to return
one row for each entry in ExpenseCategory, regardless of whether or not
there are actually any rows in InsuranceExpense for each specific category.
In that case, the expression you've got should return 0.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



KimClark said:
Thank you Mr. Steele. That makes perfect sense to me. How then, can I
use
the sum of the total of tblInsurance, which is 0 in a calculation?

Douglas J. Steele said:
If there's nothing in the table, then you get no row back, as opposed to
a
row that has a Null in it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



KimClark said:
I am creating a simple query. The table is InsuranceExpense. The field
is
InsAmt. There is nothing in the table yet. I put TtlInsurance:
Sum(CCur(Nz([InsAmt],0))) in the row marked field. I get only a blank
field.
My goal is to use the zero in a calculation with another table.
Click to expand...
Click to expand...
Click to expand...
 
Mr. Steele,
I just wanted to let you know that I used your advice here (left join) and
now my summary query works wonderful. Thank you for taking the time to help
those of us in need.
Thanks again.

Douglas J. Steele said:
Depends on what you're trying to do.

If, for example, you've got a typical parent/child setup, so that each entry
in InsuranceExpense is related to an entry in another table (call it
ExpenseCategory, for sake of argument), you could use a Left Join to return
one row for each entry in ExpenseCategory, regardless of whether or not
there are actually any rows in InsuranceExpense for each specific category.
In that case, the expression you've got should return 0.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



KimClark said:
Thank you Mr. Steele. That makes perfect sense to me. How then, can I
use
the sum of the total of tblInsurance, which is 0 in a calculation?

Douglas J. Steele said:
If there's nothing in the table, then you get no row back, as opposed to
a
row that has a Null in it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I am creating a simple query. The table is InsuranceExpense. The field
is
InsAmt. There is nothing in the table yet. I put TtlInsurance:
Sum(CCur(Nz([InsAmt],0))) in the row marked field. I get only a blank
field.
My goal is to use the zero in a calculation with another table.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top