Ms-access – MS Access 2010 adds unwanted wildcard at end of select clause

As Max says it sounds similar to a problem we had under a similar situation, especially for those working remote to the main company network.

Ensure that under the Access database Options Advanced tab that the OLE/DDE timeout is set to something like 300 and not the default of 30.

Also in any VBA requests to the database adjust the ADODB command.CommandTimeout value to a high value of at least the same 300, this will stop early timeouts and lost connections. Since we did this we have had no dropouts, it only uses the extra time as needed so do not worry about system locks from this.

Okay, well, since nobody else chimed in and I needed to get it done, I took one last shot at it. Not quite sure on the why of it all, but I used basically the entire query as a subquery in the FROM clause, and then ran the report against that, and that seemed to do it...

SELECT
  [VendorCusts].[VendorPaid],
  [VendorCusts].[DE?],
  [VendorCusts].[IC?],
  [VendorCusts].[AG?],
  [VendorCusts].[GB?],
  COUNT(VendorCustID) AS [CustCount]
FROM
  (
    SELECT DISTINCT
      [Plan Revenue Expense].[Check To] AS [VendorPaid],
      [Support Provider].[DE?],
      [Support Provider].[IC?],
      [Support Provider].[Agency?] AS [AG?],
      [Support Provider].[GeneralBus?] AS [GB?],
      Customer.CustID AS [VendorCustID],
      Customer.LName AS [VendorCustLName],
      Customer.FName AS [VendorCustFName]
    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]>=[Expense Start "MM/DD/YYYY"])
          AND
          ([Plan Revenue Expense].[First Day]<=[Expense End "MM/DD/YYYY"])
        )
        OR
        (
          ([Plan Revenue Expense].[Last Day]>=[Expense Start "MM/DD/YYYY"])
          AND
          ([Plan Revenue Expense].[Last Day]<=[Expense End "MM/DD/YYYY"])
        )
      )
      AND NOT
      (
        [Plan Revenue].[Service]='111' OR
        [Plan Revenue].[Service]='222' OR
        [Plan Revenue].[Service] LIKE '333*'
      )
      AND NOT Customer.[Inactive?]=TRUE
    ORDER BY
      [Plan Revenue Expense].[Check To],
      Customer.LName,
      Customer.FName
  ) AS [VendorCusts]
GROUP BY
  [VendorCusts].[VendorPaid],
  [VendorCusts].[DE?],
  [VendorCusts].[IC?],
  [VendorCusts].[AG?],
  [VendorCusts].[GB?]
HAVING
  NOT ([VendorCusts].[DE?]=TRUE OR [VendorCusts].[IC?]=TRUE)
ORDER BY
  [VendorCusts].[DE?] ASC,
  [VendorCusts].[IC?] ASC,
  [VendorCusts].[AG?] ASC,
  [VendorCusts].[GB?] ASC,
  [VendorCusts].[VendorPaid] ASC;

This is more or less what I finally came up with and it appears to get the job done. Checked a few data points and they seemed to agree with the slightly more verbose version w/o a Count() function. So, I think the results are good.

Then I abstracted out the start and end of the date range so I could run a few different versions. And added a few other parameters I needed...

Seems like it's working as intended, if a bit kludge-y. Sometimes it's better to be 'right' than 'pretty.' ;)