Blan Fields in Query (Access 2000/XP)

Topic

New Reply

Hans,
This a database you have been helping me with. I have attached a copy of it.
When I run my queries (qryExpeses Query and qry IncomeQuery) that ‘Sums’ up all the other Summed queries, there are blank fields which forces the Total field to be blank. It will only total if all records are populated in the query. Is there a way to default the null fields to a zero amount?
Thanks,
Bart Putnam

Reply | Quote

Replies

The Nz function can be used for this: Nz(A, returns A, except if A is Null, then it returns B. To get the SumOfGasCharge, even if there is no record for a particular date, you can use

CCur(Nz([SumOfGasCharge],0))

The Nz function replaces SumOfGasCharge by 0 if it is null (lacking), and CCur forces the result to be a currency value. The report must be modified to use the new field names.

In the attached database, I have modified only qryExpensesQuery and Expense Report this way. You should do the same for Income.

Reply | Quote

Thanks Hans

Reply | Quote

Thanks much Hans.

Is there a resource / place I can go to find the various functions (like NZ, CCur, etc.)

Also, I am curious why the query won’t give a record total if one of the fields is blank?

Again thanks,
Bart

Reply | Quote

Type Nz or CCur in a code module or in the Immediate window, then press F1. The online help system will provide information about the function.

What record total do you mean? The Total field in qryExpensesQuery returns a total even if one or more of the contributing fields is blank (null).

Reply | Quote

Sorry I haven’t gotten back to you sooner.
I realize I was wrong about totals even with a null field.

Regards,
Bart

Reply | Quote

Type Nz or CCur in a code module or in the Immediate window, then press F1. The online help system will provide information about the function.

What record total do you mean? The Total field in qryExpensesQuery returns a total even if one or more of the contributing fields is blank (null).

Reply | Quote

Thanks much Hans.

Is there a resource / place I can go to find the various functions (like NZ, CCur, etc.)

Also, I am curious why the query won’t give a record total if one of the fields is blank?

Again thanks,
Bart

Reply | Quote

The Nz function can be used for this: Nz(A, returns A, except if A is Null, then it returns B. To get the SumOfGasCharge, even if there is no record for a particular date, you can use

CCur(Nz([SumOfGasCharge],0))

The Nz function replaces SumOfGasCharge by 0 if it is null (lacking), and CCur forces the result to be a currency value. The report must be modified to use the new field names.

In the attached database, I have modified only qryExpensesQuery and Expense Report this way. You should do the same for Income.

Reply | Quote