Results 1 to 7 of 7
  1. #1
    pploum is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2014
    Posts
    5

    Dcount() only returns 0 (zero)

    Hi, I am having a strange problem. I have a small query with 6 money collection dates. I want to rank them, by counting the number of date values that are lower than each record's value. (I later want to use the rank in another function)

    CountLowerValues: DCount("*";"dd_collectiondates";"[dtl_collectiondate] <= #" & [dtl_collectiondate] & "#")



    values of [dtl_collectiondate] are 1-apr-2014, 1-may-2014, 1-jul-2014, ... The function returns 0 (zero) for each record, whereas it should be returning 1,2,3, etc

    Click image for larger version. 

Name:	Screen shot 2014-02-13 at 2.03.10 PM.png 
Views:	20 
Size:	11.8 KB 
ID:	15363



    What am I doing wrong?
    Thanks

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    You may be misunderstanding what a DCount does.
    http://www.techonthenet.com/access/f...ain/dcount.php

    I think, and it's just a guess, that you would have to use a loop construct to do what you want.

    eg
    Open your recordset
    StartLoop:
    Do until EOF
    How many records have a value less /equal to value in this record (has to process entire recordset) Dcount
    Identify value in this record and Count of NumRecords with values <=
    Move to next record
    Repeat Loop

  3. #3
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I tried to use your methodology, and it worked for me.
    But I am on a US system, so I don't know if it is a date format/default issue.

  4. #4
    pploum is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2014
    Posts
    5

    dcount returns 0

    Quote Originally Posted by orange View Post
    You may be misunderstanding what a DCount does.
    http://www.techonthenet.com/access/f...ain/dcount.php

    I think, and it's just a guess, that you would have to use a loop construct to do what you want.

    eg
    Open your recordset
    StartLoop:
    Do until EOF
    How many records have a value less /equal to value in this record (has to process entire recordset) Dcount
    Identify value in this record and Count of NumRecords with values <=
    Move to next record
    Repeat Loop
    Thank you Orange,
    I'm ok with using dcount(), the link you suggest does not help me forward, sorry.
    I also think using a loop does not address the problem of comparing dates
    Thanks anyway

  5. #5
    pploum is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2014
    Posts
    5
    hi JoeM,
    thanks for this. I have now added a field in the query, DaysToCollection (i.e. CollectionDate -/- Date()),
    and changing the criterion in Dcount() to "[DaysToCollection]<= " & [DaysToCollection.
    It works. I now tend to agree with you that is may be a format/default issue.
    Solution is cumbersome, but it works. Thanks

  6. #6
    amrut is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    India
    Posts
    616
    Have you tried formatting date like
    DCount("*";"dd_collectiondates";"[dtl_collectiondate] <= #" & Format([dtl_collectiondate],"mm\/dd\/yyyy") & "#")

  7. #7
    pploum is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2014
    Posts
    5
    not yet, but I will. Thanks for this idea.
    I'm surprised one does not need the format() function with both occurrences of [dtl_collectiondate]. Is that so?

    I do think it is really a pity that an American software (MS Access) intended for an international market, does not make -say- an internal date representation in date numbers or such when processing such a function, thus making itself really country independent.

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

Similar Threads

  1. Growing $100,000 with returns
    By Kyle02 in forum Queries
    Replies: 16
    Last Post: 08-14-2013, 02:44 PM
  2. Query returns ro results
    By MichealShinn in forum Queries
    Replies: 5
    Last Post: 02-21-2012, 01:24 PM
  3. Query returns duplicates
    By RobRay in forum Queries
    Replies: 3
    Last Post: 10-26-2010, 01:38 PM
  4. SELECT returns more than one row
    By 83dons in forum Queries
    Replies: 1
    Last Post: 01-13-2010, 11:28 AM
  5. Hard Returns v. Squares
    By Goodge12 in forum Queries
    Replies: 3
    Last Post: 01-28-2009, 10:10 AM

Tags for this Thread

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