DCount Error

[drewetzel]

Hi Guys,



I'm using the DCount function to count the number of records in a query where one date/time field is greater than or equal to another date time field and am getting the error below...

"The expression you entered as a query parameter produced this error: 'Microsoft Office Access can't find the name 'InHomeDate' you entered in the expression'

This is how I'm using the DCount function...

DCount("contact_id","Qry_CPC_FDFactor_Calls","[start_date]>=[InHomeDate]")

Before you ask, InHomeDate is definitely a field in the query, Qry_CPC_FDFactor_Calls.

Any help would be greatly appreciated.

[CraigDolphin]

try:

DCount("contact_id","Qry_CPC_FDFactor_Calls","[start_date]>= #" & [InHomeDate] & "#")

You have to use # to delimit dates/times in the same way as you'd use quotes to delimit text values. You also need to use the value in the data field, not the name of the field itself, inside the where clause of the dcount function. That's why you use the ampersand operator to build the string, referencing the field name outside of the string part.

Hopefully that makes sense. Sorry if it's unclear.

[drewetzel]

Thanks Craig. I tried that and it destroyed my database haha. It seems like it worked but the query took about 30 minutes to run. I might need to get a better computer. Anyway I think I found a way around using the DCount function. Thanks for the help though.

[CraigDolphin]

Domain aggregate functions like dcount do tend to run slow: especially if you're using them to summarize complex queries and subqueries with lots of records in the source tables.

If you can use SQL to do the same job, it should run a lot faster as a rule.

[drewetzel]

Yeah, I don't know any SQL right now, but I'm learning it. Thanks again for the help.

[pploum]

Hi, I am having a similar 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