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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|