Prev: Display calculated time in Months and Days
Next: Updated fields query
From: Wendymel on 27 Jan 2010 15:37
I got it to work...

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



"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
First  |  Prev  | 
Pages: 1 2
Prev: Display calculated time in Months and Days
Next: Updated fields query