DCount on report - per line

[justinwright]

I've seen topics about Dcounting on reports but none of them seemed to apply.

What I have is a report with a following (relevant) columns

AGENT (number datatype, field name AGENT)
6 Months
12 Months

It's a list of different agents that I want to count the number of occurrences of said agent in the table, tblData, adding that as a column to my report per line.

For example, let's say Agent #1 occurs in tblData now, 2 months ago, 7 months ago, and 14 months ago. The report would list (under separate columns) 1 for the "6 Months" column and 2 for the "12 Months" column. This will be per line for each agent listed on the report.



My DCount syntax (Control Source for the entry under the "6 Month" column) is

=DCount("*", "tblData", "AGENT = " & [txtAGENT] & " AND DATEENTRY >= #" &  [txt6mths] & "#")

but I seem to be having trouble with it.

txt6mths is just a hidden textbox I have on the report. txtAGENT is the name I assigned to the data for the AGENT column on the report.

Anyone have any ideas? Any help is much appreciated.

Justin

[pbaldy]

Are you getting an error or the wrong result? Offhand, the formula looks fine if the fields are numeric and date (you said agent was numeric, I assume the other is date). The textbox contains a date value? Can you post the db here, or a representative sample?

[justinwright]

The only result showing is 12/30/1899 (a date) when I was looking for a number. I assume this is some sort of error placeholder though.

[justinwright]

Wait nevermind, I'm retarded and I had it set to short date. When I removed the formatting, it's just giving me 0 (when if its listed on the report, it's at least a 1 in both columns.

EDIT: I was looking at older entires there too.. I really need some coffee today lol.

In short, somehow my format got set to Short Date, and removing that solved my problem based on how I had it set up. Thanks for the super quick response as always Paul!

Justin

[pbaldy]

Glad you got it sorted out Justin.