Two problems in one day:
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