Function to count unique records does not return value 1

[waubain]

In a Access report I have an unbound textbox to return a distinct count of patients on a specific drug and location. If I use a query with this SQL statement:

SELECT Count(*) AS N
FROM (SELECT DISTINCT PatientName FROM qryICU WHERE DrugGroup="Dopamine" AND LocationGroup="ICU")  AS T;

and in the textbox control source enter

=DLookUp("[N]","qryPatientCount")

I get the correct count of distinct records. But I need to repeat this about 25 times so am trying to build a function.
This is what I placed in the control source of the textbox =fPatientCount("Dopamine", "ICU") to call the function.

I have tried several multiple attmepts without success.

Function fPatientCount(strDrugGroup As String, strLocationGroup As String) As Integer

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim intCount As Long

Set db = CurrentDb

strSQL = "SELECT Count(*) AS RecordCount FROM (SELECT DISTINCT PatientName FROM qryICU WHERE DrugGroup = " & strDrugGroup & " AND LocationGroup = " & strLocationGroup & ") AS T;"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

fPatientCount = rs("RecordCount")

End Function

In the above I get a #Name? in the textbox

strSQL = "SELECT Count(*) AS RecordCount FROM (SELECT DISTINCT PatientName FROM qryICU WHERE DrugGroup = strDrugGroup AND LocationGroup = strLocationGroup) AS T;"

Here I get a Error 3061 Too few parameters. Expect 2

strSQL = "SELECT Count(*) AS RecordCount FROM (SELECT DISTINCT PatientName FROM qryICU WHERE DrugGroup = 'strDrugGroup' AND 'LocationGroup = strLocationGroup') AS T;"

This returns the number 0.00 in the textbox, so I am guessing my quotes might be off, but trying different combinations of single and double quotes returns the #Name?

Thank you.



You don't know what you don't know...

 

[PHV]

What about this ?

strSQL = "SELECT Count(*) AS RecordCount FROM (SELECT DISTINCT PatientName FROM qryICU WHERE DrugGroup=[!]'[/!]" & strDrugGroup & "[!]'[/!] AND LocationGroup=[!]'[/!]" & strLocationGroup & "[!]'[/!]) AS T"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[waubain]

Thanks PH, that worked. Frustrating as I really thought I tried that combo, but obviously not.

You don't know what you don't know...