A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Can some tell me where my formula is going wrong please



 
 
Thread Tools Display Modes
  #1  
Old May 24th, 2010, 10:15 AM posted to microsoft.public.access
Alan
external usenet poster
 
Posts: 459
Default Can some tell me where my formula is going wrong please



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
  #2  
Old May 24th, 2010, 10:59 AM posted to microsoft.public.access
RonaldoOneNil
external usenet poster
 
Posts: 345
Default Can some tell me where my formula is going wrong please

Take out the quotes and the apostrophe in this middle of this section
.... [Revenue Stream Division] = 'Postage' And ([customer no] = ...

"Alan" 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 [customer no] =
'C01317')")

thanks

  #3  
Old May 24th, 2010, 10:59 AM posted to microsoft.public.access
xps35
external usenet poster
 
Posts: 22
Default Can some tell me where my formula is going wrong please

=?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 [customer no] =
'C01317')")

thanks


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

  #4  
Old May 24th, 2010, 11:51 AM posted to microsoft.public.access
Alan
external usenet poster
 
Posts: 459
Default Can some tell me where my formula is going wrong please


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" wrote:

=?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 [customer no] =
'C01317')")

thanks


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

.

  #5  
Old May 24th, 2010, 12:14 PM posted to microsoft.public.access
Bob Quintal
external usenet poster
 
Posts: 939
Default Can some tell me where my formula is going wrong please

=?Utf-8?B?QWxhbg==?= wrote in
:



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


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','C01 317')")
  #6  
Old May 24th, 2010, 12:25 PM posted to microsoft.public.access
XPS350
external usenet poster
 
Posts: 69
Default Can some tell me where my formula is going wrong please

On 24 mei, 12:51, Alan wrote:
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" wrote:
=?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 [customer no] =
'C01317')")


thanks


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


.


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


Groeten,

Peter
http://access.xps350.com
  #7  
Old May 24th, 2010, 02:35 PM posted to microsoft.public.access
John Spencer
external usenet poster
 
Posts: 7,815
Default Can some tell me where my formula is going wrong please

=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

Alan 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 [customer no] =
'C01317')")

thanks

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:40 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.