running count in a query by group

[texasgrandma]

Query is based on 1 table" tblTimeCnv_AgeGroups

Fields:
AgeGroup Time Ranking
30-& under 11.22 1
30-& under 10.41 2
30-& under 9.22 3
30-39 11.32 1
30-39 9.53 2
30-39 9.34 3
30-39 9.30 4
see attachment

My Ranking field is:
Ranking: DCount("*","[tblTimeCNV_AgeGroups]","[AgeGroup]=" & [AgeGroup] & " and time <= " & [time])

In the Ranking column the result is: #error in the first 3 rows then zeroes

 

[John Big Booty]

Perhaps the article here will give you some pointers.

 

[plog]

2 possibilities just by eyeballing:

1. You are evaluating [AgeGroup] as a numeric value where its actually text. Since "30 - 39" will evaluate to -9 it works, but "30-& under" can only be evaluated as a string. You need single quotes around it.

2. 'time' is a reserved word in Access and can cause issues when using it in queries and codes like this. You should rename the field to something more meaningful than just 'time', I'd prefix it with what the data is for--i.e 'CNVTime'. You can force it to treat it like a field by bracketing it--[time].

 

[texasgrandma]

I made the changes you suggested. That got rid of the #errors but now I have all 0's in my ranking column.

Here's my correct formula:
Ranking: DCount("*","[tblTimeCNV_AgeGroups]","'AgeGroup'=" & 'AgeGroup' & " and timemaxHR <= " & [timemaxhr])