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(myMont h As Integer, myYear As Integer) As Integer
Dim mySQL As String
Dim cnn1 As ADODB.Connectio n
Dim myRS As New ADODB.Recordset
'Set up Recordset
Set cnn1 = CurrentProject. Connection
myRS.ActiveConn ection = cnn1
myRS.CursorType = adOpenKeyset
myRS.LockType = adLockOptimisti c
mySQL = "SELECT DISTINCTROW Count(qryCCDNAS ubmitList.Case) AS CountOfCase"
mySQL = mySQL + " FROM qryCCDNASubmitL ist WHERE (((qryCCDNASubm itList.SubMo)="
mySQL = mySQL + CStr(myMonth) + ") AND ((qryCCDNASubmi tList.SubYr)=" + CStr(myYear) + "))"
myRS.Open mySQL
'It's returning a recordcount of 1 so the problem doesn't appear to be here
If myRS.RecordCoun t = 0 Then
CCDNASub = 0
Else: CCDNASub = myRS.Fields(0)
End If
myRS.Close
Set myRS = Nothing
Set cnn1 = Nothing
End Function
Public Function CCDNASub(myMont h As Integer, myYear As Integer) As Integer
Dim mySQL As String
Dim cnn1 As ADODB.Connectio n
Dim myRS As New ADODB.Recordset
'Set up Recordset
Set cnn1 = CurrentProject. Connection
myRS.ActiveConn ection = cnn1
myRS.CursorType = adOpenKeyset
myRS.LockType = adLockOptimisti c
mySQL = "SELECT DISTINCTROW Count(qryCCDNAS ubmitList.Case) AS CountOfCase"
mySQL = mySQL + " FROM qryCCDNASubmitL ist WHERE (((qryCCDNASubm itList.SubMo)="
mySQL = mySQL + CStr(myMonth) + ") AND ((qryCCDNASubmi tList.SubYr)=" + CStr(myYear) + "))"
myRS.Open mySQL
'It's returning a recordcount of 1 so the problem doesn't appear to be here
If myRS.RecordCoun t = 0 Then
CCDNASub = 0
Else: CCDNASub = myRS.Fields(0)
End If
myRS.Close
Set myRS = Nothing
Set cnn1 = Nothing
End Function
Comment