From: Wendymel on
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
From: golfinray on
Try the NZ (no zero) command.
=([InvoiceAmount]-(nz[PPTotal])-(nz[InnTotal])-(nz[IceTotal])-(nz[FoodTotal]))
--
Milton Purdy
ACCESS
State of Arkansas


"Wendymel" wrote:

> 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
From: Wendymel on
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" wrote:

> Try the NZ (no zero) command.
> =([InvoiceAmount]-(nz[PPTotal])-(nz[InnTotal])-(nz[IceTotal])-(nz[FoodTotal]))
> --
> Milton Purdy
> ACCESS
> State of Arkansas
>
>
> "Wendymel" wrote:
>
> > 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
From: golfinray on
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" wrote:

> 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" wrote:
>
> > Try the NZ (no zero) command.
> > =([InvoiceAmount]-(nz[PPTotal])-(nz[InnTotal])-(nz[IceTotal])-(nz[FoodTotal]))
> > --
> > Milton Purdy
> > ACCESS
> > State of Arkansas
> >
> >
> > "Wendymel" wrote:
> >
> > > 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
From: Wendymel on
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" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > Try the NZ (no zero) command.
> > > =([InvoiceAmount]-(nz[PPTotal])-(nz[InnTotal])-(nz[IceTotal])-(nz[FoodTotal]))
> > > --
> > > Milton Purdy
> > > ACCESS
> > > State of Arkansas
> > >
> > >
> > > "Wendymel" wrote:
> > >
> > > > 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