I'm trying to get the result of a Count query into a variable so I can use it (basically I just need to know if it's greater than 0).

My code looks like this:

                Set qdg = db.QueryDefs("quyGpSumReportCount")
                qdg.Sql = Replace(qdg.Sql, "plugtable", VigilTable)
                qdg.Sql = Replace(qdg.Sql, "plugchurch", "'" & vChurch & "'")
                Set rst = qdg.OpenRecordset("quyGpSumReportCount")
                Debug.Print "Total = " & rst!Total
                PartCnt = rst!Total
                rst.Close
                Set rst = Nothing

The query itself looks like this for the first church once the replacements have been made:

FROM (SELECT DISTINCT t.fldUserID, v.[fldChurch/Parish] FROM tblSpring2022 _
AS t INNER JOIN tblVolunteers AS v ON t.[fldUserID] = v.[ID] WHERE _
 (v.[fldChurch/Parish] = '1548 Heights'))  AS [%$##@_Alias];

Since I don't change to the query back after running it, I can switch to the Datasheet view and see the results: In the Field Totals, there is one entry and it's value is 1. (I've run it with a couple of different churches and get, of course, different but accurate values, including 0 for a few.)

But every attempt I've made to capture the value, using the name of the field, Total, or Fields(0) or rst.Fields(0) or anything else I can think of or find on the internet, always comes up Null.

So the query is running and returning the correct result but I seem totally unable to access that result from within VBA.

What am I doing wrong?

Thanks.

CodePudding user response：

OK, I didn't solve this, you guys did; but it is solved.

For reasons that entirely escape me, qdg.OpenRecordset and qdf.OpenRecordset both resulted in a Data Conversion Error. But db.OpenRecordset, suggested above, works perfectly. Not only does it run but the result of the query finds its way both into a MsgBox and into the textbox on the form, making it possible for me to use it.

If I could upvote comments I would; the solution is, after all, there.

Thanks to all!