Ms-access – “No Current Record” when using COUNT() in Access 2010

Okay, so, I'm trying to get a count of how many customers each service provider has billed for over the last year or two. Our database's data is slightly imperfect, so I'm trying to kludge together a solution.

Basically, we have a number of service providers who we use regularly and are listed in our database with Provider IDs, but we occasionally also pay one-off providers who aren't listed formally. So, I'm querying against our expense records, trying to group by "Check To" (the name of the Provider who was paid), and where possible match it up via a Provider ID # to our formal Providers table [Support Provider], to pull some additional info where possible (provider classification DE/IC/Agency/General Business). So, I have a Left Join included, so that expense records aren't excluded if there's no matching Provider ID in the Provider table.

Everything works fine when I'm simply DISPLAYING Provider/Customer/Expense info. But when I instead go to COUNT([Customer].[CustID]) and add GROUP BY in the process, to get a handle on how many customers have been served by a given provider in the last year or two, it tells me "no current record." I'm not sure why…

SELECT DISTINCT
  [Plan Revenue Expense].[Check To],
  [Plan Revenue Expense].[SP],
  [Support Provider].[DE?],
  [Support Provider].[IC?],
  [Support Provider].[Agency?] AS [AG?],
  [Support Provider].[GeneralBus?] AS [GB?],
  COUNT(Customer.CustID)
FROM
  (((Customer
  INNER JOIN Plan ON Customer.CustID=Plan.CustID) 
  INNER JOIN [Plan Revenue] ON [Plan].[Plan ID]=[Plan Revenue].[PlanID])
  INNER JOIN [Plan Revenue Expense] ON [Plan Revenue].[Rev ID]=[Plan Revenue Expense].[RevID])
  LEFT JOIN [Support Provider] ON [Plan Revenue Expense].[SP]=[Support Provider].[ID]
WHERE
  (
    (
      ([Plan Revenue Expense].[First Day]>=#7/1/12#)
      AND
      ([Plan Revenue Expense].[First Day]<=#12/31/14#)
    )
    OR
    (
      ([Plan Revenue Expense].[Last Day]>=#7/1/12#)
      AND
      ([Plan Revenue Expense].[Last Day]<=#12/31/14#)
    )
  )
GROUP BY
  [Plan Revenue Expense].[Check To],
  [Plan Revenue Expense].[SP],
  [Support Provider].[DE?],
  [Support Provider].[IC?],
  [Support Provider].[Agency?],
  [Support Provider].[GeneralBus?]
ORDER BY
  [Support Provider].[DE?],
  [Support Provider].[IC?],
  [Support Provider].[Agency?],
  [Support Provider].[GeneralBus?],
  [Plan Revenue Expense].[Check To];

I'm not clear on why the above is returning "No Current Record" when the below returns 2602 records (I'd expect the above to return a few hundred records with "COUNT()s"):

SELECT DISTINCT
  [Plan Revenue Expense].[Check To],
  [Plan Revenue Expense].[SP],
  [Support Provider].[DE?],
  [Support Provider].[IC?],
  [Support Provider].[Agency?] AS [AG?],
  [Support Provider].[GeneralBus?] AS [GB?],
  Customer.CustID
FROM
  (((Customer
  INNER JOIN Plan ON Customer.CustID=Plan.CustID) 
  INNER JOIN [Plan Revenue] ON [Plan].[Plan ID]=[Plan Revenue].[PlanID])
  INNER JOIN [Plan Revenue Expense] ON [Plan Revenue].[Rev ID]=[Plan Revenue Expense].[RevID])
  LEFT JOIN [Support Provider] ON [Plan Revenue Expense].[SP]=[Support Provider].[ID]
WHERE
  (
    (
      ([Plan Revenue Expense].[First Day]>=#7/1/12#)
      AND
      ([Plan Revenue Expense].[First Day]<=#12/31/14#)
    )
    OR
    (
      ([Plan Revenue Expense].[Last Day]>=#7/1/12#)
      AND
      ([Plan Revenue Expense].[Last Day]<=#12/31/14#)
    )
  )
ORDER BY
  [Support Provider].[DE?],
  [Support Provider].[IC?],
  [Support Provider].[Agency?],
  [Support Provider].[GeneralBus?],
  [Plan Revenue Expense].[Check To];

It seems like somewhere in the process of switching Customer.CustID to COUNT(Customer.CustID) and adding the GROUP BY clause, it suffers a massive brain fart and returns "No current record."

I'm getting quite frustrated with this query.

What I really want it to return is a list of Providers & how many distinct customers they've been paid for rendering services to during the specified time period (regardless of how many individual expenses may have been charged).

So, if Provider X billed a hundred times, but all the expenses were for only 3 actual individual clients, I just want it to return something like:

Provider X, Provider Type(s), 3 [Customers Served During Period]

Any thoughts on why I'm getting a "No current record" when counting and/or how to fix it?

…

and I've also tried both SELECT and SELECT DISTINCT as well as joining a subquery into the FROM statement (although it didn't return "no current record" it also didn't COUNT() correctly either, counting ALL Expenses, rather than just distinct CustID's).