Expression Builder query with zero amounts

[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

 

[golfinray]

Try the NZ (no zero) command.
=([InvoiceAmount]-(nz[PPTotal])-(nz[InnTotal])-(nz[IceTotal])-(nz[FoodTotal]))

 

[Wendymel]

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

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

 

[golfinray]

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

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

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

 

[Wendymel]

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

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

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

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

 

[Wendymel]

I got it to work...

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

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

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

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