can someone tell me where my formula going wrong V2
in [General]
|
Prev: Can some tell me where my formula is going wrong please
Next: Weird tab color behavior in A2003 on Win7
From: Alan on 24 May 2010 07:33 =DSum("[Amount6a6fs]","[Sales Analysis]","[Posting Date Period] = '" & [cbMonth] & "' And [Revenue Stream Division] = 'Production' or [Revenue Stream Division] = 'other' And ([Customer No] = 'C01317')") every works apart from the last par customer no, the table name is correct spelling, the sun i'm getting is for everything not that specifc customer.
From: Douglas J. Steele on 24 May 2010 08:17 I suspect the problem is due to precedence of Or vs And. Try using IN =DSum("[Amount6a6fs]","[Sales Analysis]","[Posting Date Period] = '" & [cbMonth] & "' And [Revenue Stream Division] IN ('Production', 'other') And ([Customer No] = 'C01317')") or put parentheses around the OR =DSum("[Amount6a6fs]","[Sales Analysis]","[Posting Date Period] = '" & [cbMonth] & "' And ([Revenue Stream Division] = 'Production' or [Revenue Stream Division] = 'other') And ([Customer No] = 'C01317')") -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/djsteele Co-author: Access 2010 Solutions, published by Wiley (no e-mails, please!) "Alan" <Alan(a)discussions.microsoft.com> wrote in message news:2A6EDB79-88CA-4226-BE81-042141B5C1D7(a)microsoft.com... > =DSum("[Amount6a6fs]","[Sales Analysis]","[Posting Date Period] = '" & > [cbMonth] & "' And [Revenue Stream Division] = 'Production' or [Revenue > Stream Division] = 'other' And ([Customer No] = 'C01317')") > > every works apart from the last par customer no, the table name is correct > spelling, > the sun i'm getting is for everything not that specifc customer. >
From: Bob Quintal on 28 May 2010 18:08
=?Utf-8?B?QWxhbg==?= <Alan(a)discussions.microsoft.com> wrote in news:2A6EDB79-88CA-4226-BE81-042141B5C1D7(a)microsoft.com: > =DSum("[Amount6a6fs]","[Sales Analysis]","[Posting Date Period] = > '" & [cbMonth] & "' And [Revenue Stream Division] = 'Production' > or [Revenue Stream Division] = 'other' And ([Customer No] = > 'C01317')") > > every works apart from the last par customer no, the table name is > correct spelling, > the sun i'm getting is for everything not that specifc customer. > > Parentheses are messed up. You have unnecessary ones around the ([Customer No] = 'C01317') but more importantly are missing a pair around your two [Revenue Stream Division] = parameters. Try =DSum( "[Amount6a6fs]", "[Sales Analysis]", "[Posting Date Period] = '" & [cbMonth] & "' And ( [Revenue Stream Division] = 'Production' or [Revenue Stream Division] = 'other' ) And [Customer No] = 'C01317'" ) (all on 1 line, i've split it to show the grouping) |