Count unique customers in report header

[hithere]

I have a order report for products where I am trying to include how many unique customers ordered a particular product as a customers name may appear twice in the report. The report is populated by a form filter specifying what product to list and the order dates.



Utilizing the first product in the list, Alice Mutton, two customers have ordered the product twice and should only be included in the count once. The module for the report that I copied successfully counts the unique customers and places the value in and IDCount field in the footer. I don't want the field to appear in the footer but actually in the header.

What I cannot execute is placing that in the header as well. I tried to copy the field into the header but for some reason it just comes up blank.

[June7]

Options:

1. Create a query called CustomerCount:
SELECT DISTINCT CustomerID FROM [Ship Report];
Then in the ControlSource of textbox:
=DCount("*", "CustomerCount")

2. Create a function that opens a recordset of the Ship Report query using the sql from option 1 and gets the count of records in the recordset then call that function from textbox. I tried to do this but VBA in your mdb is not cooperating.


BTW, advise no spaces or special characters/punctuation (underscore is exception) in names.

[hithere]

Good deal. Perhaps my mdb had a corruption somewhere which is why the vba isn't working. At any rate what would the vba look like? I'm not sure how to create a function for other objects in the db.

[June7]

Option 1 works just fine. Is simple and doesn't need VBA.

The code I tried for Option 2 is like:

Function CustomerCount()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT CustomerID FROM [Ship Report];")
CustomerCount = rs.RecordCount
End Function

The function code could be in the report module or a general module.

[hithere]

Should CurrentDb be switched out for the name of my db and is CustomerCount an unbound field on the report?

[June7]

CurrentDb is an intrinsic constant and no do not substitute. CustomerCount is not a field anywhere, it is just the name of the function. Can call the function from textbox ControlSource, same as calling any function - such as the DCount().

Did you try Option 1?

[hithere]

Yes I tried Option 1 and it does count the distinct IDs. However, I am using the same report for multiple reports to display data. What I've noticed is employing
SELECT DISTINCT CustomerID FROM [Ship Report]; Then in the ControlSource of textbox: =DCount("*", "CustomerCount") will supply me with unfiltered results not limited by parameters. When a region combo box is added to the form fillter to view proudct orders by region all customers are tallyed in the customercount field not just those with orders for the product in the selected region.

[June7]

Try:

SELECT DISTINCT CustomerID, RegionID FROM [Ship Report];

or

SELECT CustomerID, RegionID FROM [Ship Report] GROUP BY CustomerID, RegionID;

Then include filter in the DCount:

=DCount("*", "CustomerCount", "RegionID=" & [RegionID])