dcount syntax help

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

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
 
=DCount("[Vendor]",'Expenses","[Vendor='Walmart' AND [Cost]>400")

A currency field stores its values as numbers, without $ or other monetary
symbol.
 
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
 
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
Click to expand...

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.
 
That was it...I appreciate it so much
Thanks
Tom

John W. Vinson said:
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
Click to expand...

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.
Click to expand...
 
Tom said:
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?
Click to expand...

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

http://www.amazon.com/Microsoft®-Of...=sr_1_1?ie=UTF8&s=books&qid=1229282438&sr=8-1

http://www.amazon.com/Building-Micr...=sr_1_2?ie=UTF8&s=books&qid=1229282438&sr=8-2

http://www.amazon.com/Microsoft®-Of...=sr_1_4?ie=UTF8&s=books&qid=1229282438&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.
 
Back
Top