Expression Builder query with zero amounts
in [Queries]
|
From: Wendymel on 27 Jan 2010 08:48 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 27 Jan 2010 09:39 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 27 Jan 2010 10:21 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 27 Jan 2010 11:28 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 27 Jan 2010 14:21
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 |