By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,921 Members | 1,466 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,921 IT Pros & Developers. It's quick & easy.

How do I use DCount() with Two Sets of Criteria

P: 1
Hello, I need your help regaring DCount. I am beginner of MS Access but need to make a database for my project using Access.

As the table below, NAME and DATE itself was refered from a Query "Production Info" and each data type is Text and Date/Time.

Expand|Select|Wrap|Line Numbers
  1.   NAME      DATE      Production   COUNT (I need these numbers)
  2.   COP1    1/1/2010        100          1
  3.   COP1    1/2/2010        120          2
  4.   COP1    1/3/2010        125          3
  5.   ......    .....      .......       .....
  6.   COP2    1/1/2009         90           1
  7.   COP2    1/2/2009         85           2
  8.   ......   ......       .......      ......
  9.   .......  ......       .......      .......
  10.   COP100   ......
(In each Property NAME, daily production is measured. Currently this query has 9,995 row. I made COUNT in a field of Current Query "Production Volume" as follow but it did not work and shows #Error. Please let me know corrected DCount description.
Expand|Select|Wrap|Line Numbers
  1. COUNT:DCount("DATE", "Production Info", "DATE <=" & [DATE] &" AND NAME =" &[NAME])
Meanwhile, I tried to make a Function in a Module as follows.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Public Function NumberingDays(ByVal QueryName As String, ByVal StrName As String, ByVal ProdDate As Date) As Long
  4.  
  5.     NumberingDays = DCount(ProdDate, QueryName, ProdDate <= [ProdDate] And StrName = [StrWellName])
  6.  
  7. End Function
I made COUNT: NumberingDays("Production Info", NAME, DATE) in a fied but it does not work and show 9,995. Could you please let me know corrected way. Also, if there is a simplest way that make my goal realized and calculation speed is faster, please let me know.

Thank you,
Feb 22 '12 #1
Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,492
I'm away most of tomorrow, but I should be back on Thursday. In the mean time, please check out Before Posting (VBA or SQL) Code for things to do and avoid when posting code here.

PS. I expect I'll be able to help if no-one has already pointed you in the right direction by then.
Feb 22 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.