Two problems in one day:

Background:

I've got report that is a conglomeration of several small subreports that summarize different pieces of the data.

The subreports generally only have the report footer visible, where I do a several different calculations.

In this case, I'm trying to count the number of distinct states that show up in the group of records. (There are three different fields for states [DepartState] [ArriveState] and [PatState], and I'd love for the Distinct Count of states to be selected from the whole group, I can live with it just coming from the [DepartState] field).

I'm blown away that there's not a standard VBA function for a distinct count. I was trying to avoid creating an additional query, as I'm trying to keep the entire DB as small as possible (Both from a functional/object list perspective, and from an overall size perspective). Also, I'd have to copy this query 5 times, as I have five different data sets that I have to create this summary report for.

In that vein, in the subreport I'm working in, I created a group header based on [DepartState] and added a unbound field "StateCounter" with the control source of =1. I've left the GroupHeader visiible for the sake of verifying that it works correctly (which it does):

However, in the Report Footer section (Where the "output" exists), the unbound field with the control source of =[StateCounter] outputs as "1." If I do =Sum([StateCounter]) I am them prompted with a MsgBox to input the value for StateCounter when I run the report.

I am thinking I've missed something silly and simple.

But again, the "best outcome" would be the distinct count from those three fields. I imagine I'm not going to be able to do that without a query first joining those fields and then Select Distinct in SQL somewhere ... right?

Click to view attachment

Hi,

Not sure I understand your setup but you could try adding a textbox in the group header with a control source of =1 and set its Running Sum property to OverAll.

Just my 2 cents...

That works ... though I'm curious why it wasn't working with a simple Sum([StateCounter]) function. Weird.

Hi,

Glad to hear you got it to work. The Sum() or Count() function works against the records.

Good luck with your project.