Dcount() only returns 0 (zero)

[pploum]

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



What am I doing wrong?


Thanks

[orange]

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

[JoeM]

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.

[pploum]

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

[pploum]

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

[amrut]

Have you tried formatting date like
DCount("*";"dd_collectiondates";"[dtl_collectiondate] <= #" & Format([dtl_collectiondate],"mm\/dd\/yyyy") & "#")

[pploum]

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.