Results 1 to 7 of 7
  1. #1
    seantnash is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    6

    Question DCount Multiple Criteria Not Working

    I have the following DCount expression in my MS Access Query but it's not quite working how I want it to:



    Code:
    DCount("MemberNo","Recs","Recs.WIDate<=WIDate AND MemberNo=" & Recs.MemberNo)
    It seems to be counting all the WIDates before / equal to the WIDate that the query calls up rather than just the ones associated with the specific MemberNo.

    Any ideas please? It's probably something *really* simple - like a misplaced quotation mark - but I just can't figure it out

    Thanks in advance!

  2. #2
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,955
    proabaly this

    "Recs.WIDate<=WIDate AND MemberNo=" & Recs.MemberNo

    should be this

    "WIDate<=" & Recs.WIDate & " AND MemberNo=" & Recs.MemberNo

  3. #3
    seantnash is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    6
    Just tried it and that just returns 0 unfortunately

  4. #4
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,955
    what is your full query, including the dcount

  5. #5
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    The criteria part of your DCount doesn't make sense; you are counting records in the table "Recs", but then you refer to a field in the table (Recs.MemberNo) outside the quotation marks. Where is the value for Recs.MemberNo supposed to come from?

    I think you want this: "Recs.WIDate<= " & WIDate & " AND Recs.MemberNo=" & MemberNo as the criteria.

    Where are the values for underlined WIDate and MemberNo coming from?

  6. #6
    seantnash is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    6
    The underlined ones are values generated by the query

    I have this expression in my query that tells me the total number of occurances of MemberNo in the WIRecs table:

    Code:
    DCount("MemberNo","WIRecs","MemberNo=" & WIRecs.MemberNo)
    This works perfectly so I know this part is correct - it's just when I try and add the WIDate criteria that the problem occurs.

    Using your criteria John just caused it to say that MemberNo could refer to more than one table (memberno tends to be my primary key in a few of my tables)

  7. #7
    seantnash is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    6
    Just worked out what the problem has been. It needed # before and after the date bit .... d'oh. Thanks for trying to help guys

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 02-26-2014, 05:06 PM
  2. DCount with Multiple Criteria
    By Newbie11 in forum Reports
    Replies: 4
    Last Post: 05-25-2012, 09:04 AM
  3. How do you use DCOUNT for multiple criteria?
    By wwhit in forum Programming
    Replies: 5
    Last Post: 05-15-2012, 11:14 AM
  4. Multiple Criteria in query not working
    By avarusbrightfyre in forum Queries
    Replies: 3
    Last Post: 04-17-2012, 05:06 PM
  5. Replies: 3
    Last Post: 10-13-2010, 03:35 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums