Why is my vba sql not returning the correct value for a field?

[Catherine Dooley]

I'm using Access 2007. I have a fairly simple function (code below) that is meant to get a count of 1 field based on criteria in 2 other fields. It works fine when I copy the sql into a query, but when I run the function it returns 0 as the value. I'm sure there is something simple I'm missing, but I can't figure out what. Any ideas?

Public Function CCDNASub(myMonth As Integer, myYear As Integer) As Integer
Dim mySQL As String
Dim cnn1 As ADODB.Connection
Dim myRS As New ADODB.Recordset
'Set up Recordset
Set cnn1 = CurrentProject.Connection
myRS.ActiveConnection = cnn1
myRS.CursorType = adOpenKeyset
myRS.LockType = adLockOptimistic
mySQL = "SELECT DISTINCTROW Count(qryCCDNASubmitList.Case) AS CountOfCase"
mySQL = mySQL + " FROM qryCCDNASubmitList WHERE (((qryCCDNASubmitList.SubMo)="
mySQL = mySQL + CStr(myMonth) + ") AND ((qryCCDNASubmitList.SubYr)=" + CStr(myYear) + "))"

myRS.Open mySQL
'It's returning a recordcount of 1 so the problem doesn't appear to be here
If myRS.RecordCount = 0 Then
CCDNASub = 0
Else: CCDNASub = myRS.Fields(0)
End If
myRS.Close
Set myRS = Nothing
Set cnn1 = Nothing

End Function

[Catherine Dooley]

Actually, never mind. I've been working on this for days and I just fixed it by changing the sql. I still don't know why the previous one didn't work, but I've got a working version now.