Expression Builder query with zero amounts

  • Thread starter Thread starter Wendymel
  • Start date Start date
W

Wendymel

I am using MS Access 2007 and I am having a problem building an expression in
Expression Builder for a Control Source for a report.

I have a totals page where there is an object representing a grand total of
ALL invoices for gas. It has the Control Source query:


=Sum([Invoice Amount])


Now, in the same section (the report footer) I have the invoice amounts
broken out by 5 major buildings. Each contains an object containting the
following queries (one for each of the 5)

=IIf(IsError([PPTotal]),0,[PPTotal])

=IIf(IsError([InnTotal]),0,[InnTotal])

=IIf(IsError([AddTotal]),0,[AddTotal])

=IIf(IsError([IceTotal]),0,[IceTotal])

=IIf(IsError([FoodTotal]),0,[FoodTotal])

These queries all work perfectly to total the gas invoices for those
buildings individually. Then there is the 6th object. It is intended to
show the grand total of "all other" buildings gas invoices MINUS the 5 major
buildings. I wrote an expression query in its control source as follows:

=([InvoiceAmount]-[PPTotal]-[InnTotal]-[IceTotal]-[FoodTotal])

While it works perfectly in most cases, I have found that it WILL NOT work
and in fact errors out when there is a month that one of the major buildings
has a zero (no invoice) balance. In other words, there were no bills for that
building that month (which will happen if they are under construction or shut
down during an off season).

Can somebody please tell me what I am missing. I am so close but I am sure
I am missing a small element. I thought I could use an IIf statement but it
would not work for the complex equation.

Thanks,

Wendy
 
Try the NZ (no zero) command.
=([InvoiceAmount]-(nz[PPTotal])-(nz[InnTotal])-(nz[IceTotal])-(nz[FoodTotal]))
 
Thanks but it didn't work. It came back with an error:

"The expression you entered contains invalid syntax"
"You may have entered an operand without an operator."

Wendy

golfinray said:
Try the NZ (no zero) command.
=([InvoiceAmount]-(nz[PPTotal])-(nz[InnTotal])-(nz[IceTotal])-(nz[FoodTotal]))
--
Milton Purdy
ACCESS
State of Arkansas


Wendymel said:
I am using MS Access 2007 and I am having a problem building an expression in
Expression Builder for a Control Source for a report.

I have a totals page where there is an object representing a grand total of
ALL invoices for gas. It has the Control Source query:


=Sum([Invoice Amount])


Now, in the same section (the report footer) I have the invoice amounts
broken out by 5 major buildings. Each contains an object containting the
following queries (one for each of the 5)

=IIf(IsError([PPTotal]),0,[PPTotal])

=IIf(IsError([InnTotal]),0,[InnTotal])

=IIf(IsError([AddTotal]),0,[AddTotal])

=IIf(IsError([IceTotal]),0,[IceTotal])

=IIf(IsError([FoodTotal]),0,[FoodTotal])

These queries all work perfectly to total the gas invoices for those
buildings individually. Then there is the 6th object. It is intended to
show the grand total of "all other" buildings gas invoices MINUS the 5 major
buildings. I wrote an expression query in its control source as follows:

=([InvoiceAmount]-[PPTotal]-[InnTotal]-[IceTotal]-[FoodTotal])

While it works perfectly in most cases, I have found that it WILL NOT work
and in fact errors out when there is a month that one of the major buildings
has a zero (no invoice) balance. In other words, there were no bills for that
building that month (which will happen if they are under construction or shut
down during an off season).

Can somebody please tell me what I am missing. I am so close but I am sure
I am missing a small element. I thought I could use an IIf statement but it
would not work for the complex equation.

Thanks,

Wendy
Click to expand...
Click to expand...
 
Lookup help on nz. I probably got the syntax wrong for you particular
formula. Or let the expression builder put it in for you.
--
Milton Purdy
ACCESS
State of Arkansas


Wendymel said:
Thanks but it didn't work. It came back with an error:

"The expression you entered contains invalid syntax"
"You may have entered an operand without an operator."

Wendy

golfinray said:
Try the NZ (no zero) command.
=([InvoiceAmount]-(nz[PPTotal])-(nz[InnTotal])-(nz[IceTotal])-(nz[FoodTotal]))
--
Milton Purdy
ACCESS
State of Arkansas


Wendymel said:
I am using MS Access 2007 and I am having a problem building an expression in
Expression Builder for a Control Source for a report.

I have a totals page where there is an object representing a grand total of
ALL invoices for gas. It has the Control Source query:


=Sum([Invoice Amount])


Now, in the same section (the report footer) I have the invoice amounts
broken out by 5 major buildings. Each contains an object containting the
following queries (one for each of the 5)

=IIf(IsError([PPTotal]),0,[PPTotal])

=IIf(IsError([InnTotal]),0,[InnTotal])

=IIf(IsError([AddTotal]),0,[AddTotal])

=IIf(IsError([IceTotal]),0,[IceTotal])

=IIf(IsError([FoodTotal]),0,[FoodTotal])

These queries all work perfectly to total the gas invoices for those
buildings individually. Then there is the 6th object. It is intended to
show the grand total of "all other" buildings gas invoices MINUS the 5 major
buildings. I wrote an expression query in its control source as follows:

=([InvoiceAmount]-[PPTotal]-[InnTotal]-[IceTotal]-[FoodTotal])

While it works perfectly in most cases, I have found that it WILL NOT work
and in fact errors out when there is a month that one of the major buildings
has a zero (no invoice) balance. In other words, there were no bills for that
building that month (which will happen if they are under construction or shut
down during an off season).

Can somebody please tell me what I am missing. I am so close but I am sure
I am missing a small element. I thought I could use an IIf statement but it
would not work for the complex equation.

Thanks,

Wendy
Click to expand...
Click to expand...
Click to expand...
 
Well, I actually did look up help on nz and it led me nowhere. That is why I
wrote to this discussion group. I have tried so many different ways to get
this to work that I am going in circles.

Wendy

golfinray said:
Lookup help on nz. I probably got the syntax wrong for you particular
formula. Or let the expression builder put it in for you.
--
Milton Purdy
ACCESS
State of Arkansas


Wendymel said:
Thanks but it didn't work. It came back with an error:

"The expression you entered contains invalid syntax"
"You may have entered an operand without an operator."

Wendy

golfinray said:
Try the NZ (no zero) command.
=([InvoiceAmount]-(nz[PPTotal])-(nz[InnTotal])-(nz[IceTotal])-(nz[FoodTotal]))
--
Milton Purdy
ACCESS
State of Arkansas


:

I am using MS Access 2007 and I am having a problem building an expression in
Expression Builder for a Control Source for a report.

I have a totals page where there is an object representing a grand total of
ALL invoices for gas. It has the Control Source query:


=Sum([Invoice Amount])


Now, in the same section (the report footer) I have the invoice amounts
broken out by 5 major buildings. Each contains an object containting the
following queries (one for each of the 5)

=IIf(IsError([PPTotal]),0,[PPTotal])

=IIf(IsError([InnTotal]),0,[InnTotal])

=IIf(IsError([AddTotal]),0,[AddTotal])

=IIf(IsError([IceTotal]),0,[IceTotal])

=IIf(IsError([FoodTotal]),0,[FoodTotal])

These queries all work perfectly to total the gas invoices for those
buildings individually. Then there is the 6th object. It is intended to
show the grand total of "all other" buildings gas invoices MINUS the 5 major
buildings. I wrote an expression query in its control source as follows:

=([InvoiceAmount]-[PPTotal]-[InnTotal]-[IceTotal]-[FoodTotal])

While it works perfectly in most cases, I have found that it WILL NOT work
and in fact errors out when there is a month that one of the major buildings
has a zero (no invoice) balance. In other words, there were no bills for that
building that month (which will happen if they are under construction or shut
down during an off season).

Can somebody please tell me what I am missing. I am so close but I am sure
I am missing a small element. I thought I could use an IIf statement but it
would not work for the complex equation.

Thanks,

Wendy
Click to expand...
Click to expand...
Click to expand...
 
I got it to work...

=([InvoiceAmount]-nz([PPTotal])-nz([InnTotal])-nz([IceTotal])-nz([FoodTotal]))



golfinray said:
Lookup help on nz. I probably got the syntax wrong for you particular
formula. Or let the expression builder put it in for you.
--
Milton Purdy
ACCESS
State of Arkansas


Wendymel said:
Thanks but it didn't work. It came back with an error:

"The expression you entered contains invalid syntax"
"You may have entered an operand without an operator."

Wendy

golfinray said:
Try the NZ (no zero) command.
=([InvoiceAmount]-(nz[PPTotal])-(nz[InnTotal])-(nz[IceTotal])-(nz[FoodTotal]))
--
Milton Purdy
ACCESS
State of Arkansas


:

I am using MS Access 2007 and I am having a problem building an expression in
Expression Builder for a Control Source for a report.

I have a totals page where there is an object representing a grand total of
ALL invoices for gas. It has the Control Source query:


=Sum([Invoice Amount])


Now, in the same section (the report footer) I have the invoice amounts
broken out by 5 major buildings. Each contains an object containting the
following queries (one for each of the 5)

=IIf(IsError([PPTotal]),0,[PPTotal])

=IIf(IsError([InnTotal]),0,[InnTotal])

=IIf(IsError([AddTotal]),0,[AddTotal])

=IIf(IsError([IceTotal]),0,[IceTotal])

=IIf(IsError([FoodTotal]),0,[FoodTotal])

These queries all work perfectly to total the gas invoices for those
buildings individually. Then there is the 6th object. It is intended to
show the grand total of "all other" buildings gas invoices MINUS the 5 major
buildings. I wrote an expression query in its control source as follows:

=([InvoiceAmount]-[PPTotal]-[InnTotal]-[IceTotal]-[FoodTotal])

While it works perfectly in most cases, I have found that it WILL NOT work
and in fact errors out when there is a month that one of the major buildings
has a zero (no invoice) balance. In other words, there were no bills for that
building that month (which will happen if they are under construction or shut
down during an off season).

Can somebody please tell me what I am missing. I am so close but I am sure
I am missing a small element. I thought I could use an IIf statement but it
would not work for the complex equation.

Thanks,

Wendy
Click to expand...
Click to expand...
Click to expand...
 
Back
Top