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 » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

dcount syntax help



 
 
Thread Tools Display Modes
  #1  
Old December 10th, 2008, 01:05 PM posted to microsoft.public.access.tablesdbdesign
Tom
external usenet poster
 
Posts: 1,359
Default dcount syntax help

I have a databse with a table called Expenses. In that table I have a field
calles Vendors which is a text field and a field called Cost which is a
currency field.
I need to count the number of times that for example something was purchaed
from Walmart so I used the syntax
=dcount("[Vendor]","Expenses","[Vendor]='Walmart'") and that works

my problem is with multiple conditions
I need to count the number of times the vendor was walmart and the cost was
more than $400.00

I tried
=dcount("[Vendor]",'Expenses",'[Vendor='Walmart' AND [Cost]'$400.00'') and
all I get is an error any suggestions would be most appreciated . Thanks is
advance

Tom
  #2  
Old December 10th, 2008, 01:22 PM posted to microsoft.public.access.tablesdbdesign
Ken Snell \(MVP\)
external usenet poster
 
Posts: 2,506
Default dcount syntax help

=DCount("[Vendor]",'Expenses","[Vendor='Walmart' AND [Cost]400")

A currency field stores its values as numbers, without $ or other monetary
symbol.
--

Ken Snell
MS ACCESS MVP
http://www.accessmvp.com/KDSnell/



"Tom" wrote in message
...
I have a databse with a table called Expenses. In that table I have a field
calles Vendors which is a text field and a field called Cost which is a
currency field.
I need to count the number of times that for example something was
purchaed
from Walmart so I used the syntax
=dcount("[Vendor]","Expenses","[Vendor]='Walmart'") and that works

my problem is with multiple conditions
I need to count the number of times the vendor was walmart and the cost
was
more than $400.00

I tried
=dcount("[Vendor]",'Expenses",'[Vendor='Walmart' AND [Cost]'$400.00'')
and
all I get is an error any suggestions would be most appreciated . Thanks
is
advance

Tom



  #3  
Old December 10th, 2008, 01:44 PM posted to microsoft.public.access.tablesdbdesign
Tom
external usenet poster
 
Posts: 1,359
Default dcount syntax help

Thanks Ken that was it....I appreciate the guidence

"Tom" wrote:

I have a databse with a table called Expenses. In that table I have a field
calles Vendors which is a text field and a field called Cost which is a
currency field.
I need to count the number of times that for example something was purchaed
from Walmart so I used the syntax
=dcount("[Vendor]","Expenses","[Vendor]='Walmart'") and that works

my problem is with multiple conditions
I need to count the number of times the vendor was walmart and the cost was
more than $400.00

I tried
=dcount("[Vendor]",'Expenses",'[Vendor='Walmart' AND [Cost]'$400.00'') and
all I get is an error any suggestions would be most appreciated . Thanks is
advance

Tom

  #4  
Old December 11th, 2008, 01:07 PM posted to microsoft.public.access.tablesdbdesign
Tom
external usenet poster
 
Posts: 1,359
Default One more Dcount Question

One more Think Ken

First of all I get so confused in access as to when I use quotations and
when to use exclamation points and in general how to refer to fields that are
not in the form or table that I am using at the moment. Do you know a good
reference that could explain these for me so I can get a handle on them?

second of all in the same table I would like a dcount of all vendors where I
spent more than 20 but less than 200.00 I tried this
=dcount('[Vendor]","Expenses","([Cost]'20' AND [Cost]'200')) and it keeps
giving me an error. It is very frustrating and I hate to bother you with this
but I just need some guidence. Thanks again



"Tom" wrote:

I have a databse with a table called Expenses. In that table I have a field
calles Vendors which is a text field and a field called Cost which is a
currency field.
I need to count the number of times that for example something was purchaed
from Walmart so I used the syntax
=dcount("[Vendor]","Expenses","[Vendor]='Walmart'") and that works

my problem is with multiple conditions
I need to count the number of times the vendor was walmart and the cost was
more than $400.00

I tried
=dcount("[Vendor]",'Expenses",'[Vendor='Walmart' AND [Cost]'$400.00'') and
all I get is an error any suggestions would be most appreciated . Thanks is
advance

Tom

  #5  
Old December 11th, 2008, 06:32 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default One more Dcount Question

On Thu, 11 Dec 2008 04:07:01 -0800, Tom wrote:

One more Think Ken

First of all I get so confused in access as to when I use quotations and
when to use exclamation points and in general how to refer to fields that are
not in the form or table that I am using at the moment. Do you know a good
reference that could explain these for me so I can get a handle on them?

second of all in the same table I would like a dcount of all vendors where I
spent more than 20 but less than 200.00 I tried this
=dcount('[Vendor]","Expenses","([Cost]'20' AND [Cost]'200')) and it keeps
giving me an error. It is very frustrating and I hate to bother you with this
but I just need some guidence. Thanks again


Lose the single quotes around the Cost criteria.

Text field criteria must be delimited with either ' or " quotemarks.
Date field criteria must be delimited with #.
Number or Currency field criteria get no delimiters at all.
--

John W. Vinson [MVP]
  #6  
Old December 11th, 2008, 06:49 PM posted to microsoft.public.access.tablesdbdesign
Tom
external usenet poster
 
Posts: 1,359
Default One more Dcount Question

That was it...I appreciate it so much
Thanks
Tom

"John W. Vinson" wrote:

On Thu, 11 Dec 2008 04:07:01 -0800, Tom wrote:

One more Think Ken

First of all I get so confused in access as to when I use quotations and
when to use exclamation points and in general how to refer to fields that are
not in the form or table that I am using at the moment. Do you know a good
reference that could explain these for me so I can get a handle on them?

second of all in the same table I would like a dcount of all vendors where I
spent more than 20 but less than 200.00 I tried this
=dcount('[Vendor]","Expenses","([Cost]'20' AND [Cost]'200')) and it keeps
giving me an error. It is very frustrating and I hate to bother you with this
but I just need some guidence. Thanks again


Lose the single quotes around the Cost criteria.

Text field criteria must be delimited with either ' or " quotemarks.
Date field criteria must be delimited with #.
Number or Currency field criteria get no delimiters at all.
--

John W. Vinson [MVP]

  #7  
Old December 14th, 2008, 08:26 PM posted to microsoft.public.access.tablesdbdesign
Ken Snell \(MVP\)
external usenet poster
 
Posts: 2,506
Default One more Dcount Question

"Tom" wrote in message
...
One more Think Ken

First of all I get so confused in access as to when I use quotations and
when to use exclamation points and in general how to refer to fields that
are
not in the form or table that I am using at the moment. Do you know a good
reference that could explain these for me so I can get a handle on them?


Any good book on ACCESS will give you information about the above items. For
examples:

http://www.amazon.com/Microsoft%C2%A...9282438&sr=8-1

http://www.amazon.com/Building-Micro...9282438&sr=8-2

http://www.amazon.com/Microsoft%C2%A...9282438&sr=8-4


Briefly, you use quotations to delimit text string data. An exclamation
point is used to separate an object from a reference to its default
collection (e.g., Forms!NameOfForm!NameOfControlOnForm = the default
collection of Forms is the Forms collection, of which the "NameOfForm" is
one of those forms; and the default collection of Form (the actual form) is
the Controls collection, of which "NameOfControlOnForm" is one of those
controls.). A dot (".") is used to separate an object from one of its
properties (a value of something) or one of its methods (a function for
doing something); for a Form, all fields from the form's recordset and all
controls from the form's Controls collection are added as properties of the
form, so you can reference them via . instead of !. But this is not a
general statement that applies to all ACCESS objects.


 




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 01:59 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.