Can some tell me where my formula is going wrong please

  • Thread starter Thread starter Alan
  • Start date Start date
A

Alan

can some please tell what wrong with my dsum

=DSum("[Amount6a6fs]","[Sales Analysis]","[Posting Date Period] = '" &
[cbMonth] & "' And [Revenue Stream Division] = 'Postage'" And "'([customer
no] = 'IC0008' or [customer no] = 'C01105' or [customer no] = 'C00785' or
[customer no] = 'C01388' or [customer no] = 'IC0003' or [customer no] =
'C01317')")

thanks
 
Take out the quotes and the apostrophe in this middle of this section
.... [Revenue Stream Division] = 'Postage' And ([customer no] = ...
 
=?Utf-8?B?QWxhbg==?= said:
can some please tell what wrong with my dsum

=DSum("[Amount6a6fs]","[Sales Analysis]","[Posting Date Period] = '" &
[cbMonth] & "' And [Revenue Stream Division] = 'Postage'" And "'([customer
no] = 'IC0008' or [customer no] = 'C01105' or [customer no] = 'C00785' or
[customer no] = 'C01388' or [customer no] = 'IC0003' or [customer no] =
'C01317')")

thanks
Click to expand...

Hard te tell without any further information.

Do you get an error? If so, which?
Is the result no what you expect?

One thing could be [Posting Date Period]. What type is it?
 
its kind of work but the last part doesn't p compile as i just getting the
total value for that month and not specific customer

XPS35 said:
=?Utf-8?B?QWxhbg==?= said:
can some please tell what wrong with my dsum

=DSum("[Amount6a6fs]","[Sales Analysis]","[Posting Date Period] = '" &
[cbMonth] & "' And [Revenue Stream Division] = 'Postage'" And "'([customer
no] = 'IC0008' or [customer no] = 'C01105' or [customer no] = 'C00785' or
[customer no] = 'C01388' or [customer no] = 'IC0003' or [customer no] =
'C01317')")

thanks
Click to expand...

Hard te tell without any further information.

Do you get an error? If so, which?
Is the result no what you expect?

One thing could be [Posting Date Period]. What type is it?

--
Groeten,

Peter
http://access.xps350.com

.
Click to expand...
 
can some please tell what wrong with my dsum

=DSum("[Amount6a6fs]","[Sales Analysis]","[Posting Date Period] =
'" & [cbMonth] & "' And [Revenue Stream Division] = 'Postage'" And
"'([customer no] = 'IC0008' or [customer no] = 'C01105' or
[customer no] = 'C00785' or [customer no] = 'C01388' or [customer
no] = 'IC0003' or [customer no] = 'C01317')")

thanks
Click to expand...

several issues, mostly with quote symbols,in the [customer no]
criteria. using the IN method simplifies that.
Also note that the quote marks around cbmonth indicate a text type for
[Posting Date Period]. If it is a number, remove the single quote
before [cbmonth] and after. Also if cbmonth is a control on a form, you
may have a problem unless you specify the name of the form, as
Forms!myForm!cbMonth instead of [cbMonth] (change myForm to your form's
name)

This should work

=DSum("[Amount6a6fs]", "[Sales Analysis]",
"[Posting Date Period] = '" & [cbMonth] & "'
And [Revenue Stream Division] = 'Postage'
And [customer no] IN
('IC0008','C01105','C00785','C01388','IC0003','C01317')")
 
its kind of work but the last part doesn't p compile as i just getting the
total value for that month and not specific customer

XPS35 said:
=?Utf-8?B?QWxhbg==?= wrote:
can some please tell what wrong with my dsum
=DSum("[Amount6a6fs]","[Sales Analysis]","[Posting Date Period] ='" &
[cbMonth] & "' And [Revenue Stream Division] = 'Postage'" And "'([customer
no] = 'IC0008' or [customer no] = 'C01105' or [customer no] = 'C00785'  or
[customer no] = 'C01388' or [customer no] = 'IC0003' or [customerno] =
'C01317')")
thanks
Click to expand...
Click to expand...
Hard te tell without any further information.
Click to expand...
Do you get an error? If so, which?
Is the result no what you expect?
Click to expand...
One thing could be [Posting Date Period]. What type is it?
Peter
http://access.xps350.com
Click to expand...

.
Click to expand...
Click to expand...

A least there is an & missing in:
.... [Revenue Stream Division] = 'Postage'" And "'([customer no] =...


Groeten,

Peter
http://access.xps350.com
 
=DSum("[Amount6a6fs]","[Sales Analysis]","[Posting Date Period] = '" &
[cbMonth] & "' And [Revenue Stream Division] = 'Postage'
And ([customer no] = 'IC0008' or [customer no] = 'C01105'
or [customer no] = 'C00785' or [customer no] = 'C01388'
or [customer no] = 'IC0003' or [customer no] = 'C01317')")

A little bit easier to read would be

=DSum("[Amount6a6fs]","[Sales Analysis]","[Posting Date Period] = '" &
[cbMonth] & "' And [Revenue Stream Division] = 'Postage'
And [customer no]
IN ('IC0008','C01105', 'C00785', 'C01388', 'IC0003', 'C01317')")



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top