Counting Unique Dates in a Report? 1

[MrKABC]

Hello all:

I am trying to get a count of unique dates in a report. The date field in the Detail section is formatted as a general date. The dates are at random entered by the end user. I want to count each unique date entered by the user. The data looks like:

1-1-2009
1-1-2009
1-2-2009
1-3-2009
1-3-2009

I would like the end result to be "3" for the example above, as in "there were three different dates entered."

I tried creating a text field in the report footer using:

 =Sum(Abs([Date])

but that gave me some really wacky number that wasn't anything close to reality.

I also tried creating an expression in my report query that looks like this:

Expr1: (SELECT Count(*) FROM (SELECT DISTINCT Date FROM ExpenseTbl) )

The subquery works great by itself, but when I try to use it in a report I get the following error:
"Multi-level GROUP BY clause is not allowed in a subquery".

Now I am not using any grouping levels in my report (I stripped them out) so I am now at a loss.

Any help would be appreciated.

A.

PS: Access 2003 question.

 

[dhookom]

You could group by date with a date footer. Add a text box to the date footer:
[tt][blue]
Name: txtCountDate
Control Source: =1
Running Sum: Over All
Visible: No
[/blue][/tt]
Then add a text box to the report footer:
[tt][blue]
Name: txtCountDateTot
Control Source: =txtCountDate
Running Sum: None
Visible: Yes
[/blue][/tt]


Duane
Hook'D on Access
MS Access MVP

 

[MrKABC]

Duane, your remedy worked like a charm.

Thank you VERY much!!!

A star for you - I really really appreciate it!

A.