MS Access: Unexpected results when using DCount in a nested query with a VBA function
I've tried everything I can think of but I'm collaborating on a project where I'm working on a project and hit a roadblock. I'm working with an scenario while trying to use the `DCount` function within a nested query in MS Access (version 2016). The goal is to count how many records in a linked table match a specific condition for each record in my main table. However, I get inconsistent results that don't seem to reflect the actual data. Here’s the SQL for the nested query I’m working with: ```sql SELECT t1.ID, t1.Name, (SELECT COUNT(*) FROM LinkedTable AS t2 WHERE t2.RelatedID = t1.ID AND t2.Status = 'Active') AS ActiveCount FROM MainTable AS t1; ``` When I run this query, the `ActiveCount` returns zero for some records which I know should not be the case. After checking, I confirmed that there are indeed matching records in `LinkedTable` that satisfy the criteria. Furthermore, I have a VBA function that I’m using to manipulate data before it’s displayed: ```vba Function GetActiveCount(ID As Long) As Long GetActiveCount = DCount("*", "LinkedTable", "RelatedID = " & ID & " AND Status = 'Active'") End Function ``` I attempted to call this function in my main query instead of the nested `DCount`, like this: ```sql SELECT t1.ID, t1.Name, GetActiveCount(t1.ID) AS ActiveCount FROM MainTable AS t1; ``` However, this results in a runtime behavior: "Function is not available in the expression." I’ve ensured that my VBA module is public and the function is declared correctly. I also tried to explicitly specify the library reference but to no avail. Has anyone else run into this question? What could be going wrong with my `DCount` or the VBA function in this context? Any insights would be appreciated! I'm working on a API that needs to handle this. How would you solve this? For context: I'm using Vba on Debian. Any ideas how to fix this?