Problem with DCount function

[teel73]

I have a control on a report that has a user defined function as the controlsource. Basically I want to count the number of records where the recvdate is equal to the completeddate in the table. For example:

[recvddate] = 9/18/07 and [completeddate] = 9/18/07

this record would get counted. Below is the syntax that I have but it is resulting in 0 records. What am I doing wrong with this syntax.

=dcount("id","tblPercentages","[recvddate] = [completeddate]")

 

[boblarson]

I have a control on a report that has a user defined function as the controlsource. Basically I want to count the number of records where the recvdate is equal to the completeddate in the table. For example:

[recvddate] = 9/18/07 and [completeddate] = 9/18/07

this record would get counted. Below is the syntax that I have but it is resulting in 0 records. What am I doing wrong with this syntax.

=dcount("id","tblPercentages","[recvddate] = [completeddate]")

try this:

=dcount("id","tblPercentages","[recvddate][b][color=red]"[/color][/b] = [b][color=red]"[/color][/b][completeddate]")

 

[Alc]

Could it be that the date is being stored too precisely for what you need? i.e. if it is shown as day, month, year but stored to include hours, minutes and seconds, you'd be unlikely to find an exact match.
If this is the case, you could try this, so the comparison is made at just the date level:

=dcount("id","tblPercentages","Format([recvddate],'DDMMYYYY') = Format([completeddate],'DDMMYYYY"")

 

[teel73]

thank you but that didn't work either. this is really puzzling me. I never had to use criteria where 1 field's value is equal to another field's value. I 've always used criteria where 1 field's value is equal to a value on a form or on a report.

 

[boblarson]

thank you but that didn't work either. this is really puzzling me. I never had to use criteria where 1 field's value is equal to another field's value. I 've always used criteria where 1 field's value is equal to a value on a form or on a report.

Then it might have to do with Alc's post (which he deleted, but I've resurrected). See the post here

http://www.access-programmers.co.uk/forums/showpost.php?p=633186&postcount=3

 

[Alc]

Sorry.
Saw your reply and it made sense to me, so I didn't want to confuse things.
I was just about to repost it.

 

[boblarson]

Sorry.
Saw your reply and it made sense to me, so I didn't want to confuse things.
I was just about to repost it.

Thanks for jumping in with it though. We'll see where it leads. I am way more visual and actually being able to see and play with the actual database makes it 100 times easier for me to troubleshoot than not. So, sometimes we have those situations where we aren't getting the full story or the poster doesn't provide information because they either don't know it's important or they don't even know it's there.

 

[boblarson]

Can you post the db?

 

[teel73]

thanks guys. I fixed it. The error was on me. I was mispelling the field. My first syntax worked.

=dcount("id","tblPercentages","[recvddate] = [completeddate]")


I apologize for the inconvenience of even entertaining such an amatuer situation.

 

[boblarson]

I apologize for the inconvenience of even entertaining such an amatuer situation.

No apologies necessary. I can't tell you the number of times I've done that as well, so don't worry about it.