SQL QUERY in FUNCTION returns 0 value

[JEH]

I am attempting to use a SQL query within a function to return a value of the
number of records meeting the conditions of the query. I have been
(successfully) up to this point using the SQL view on the Access query
builder to construct the SQL query for use in the function.

However, my intTotalClearances FUNCTION consistently returns a 0 value,
despite the fact that the identically worded query in ACCESS returns > 600
records. Here's the function:

Public Function intTotalClearances() As Integer

Dim strSQL As String
Dim adoRst As ADODB.Recordset

' Determine total Clearances
strSQL = "SELECT * FROM [tblBde SCAR] WHERE ((([tblBde
SCAR].CLR_ELIGIBLE) Like 'S*' Or ([tblBde SCAR].CLR_ELIGIBLE) Like 'T*' Or
([tblBde SCAR].CLR_ELIGIBLE) Like 'C*') AND (([tblBde SCAR].CLR_ACCESS) Not
Like '*DENIED' Or ([tblBde SCAR].CLR_ACCESS) Not Like '*SUS*' Or ([tblBde
SCAR].CLR_ACCESS) Not Like '*REV*'))"

'Set up dB connection and recordset
Set adoRst = New ADODB.Recordset
adoRst.ActiveConnection = CurrentProject.Connection
adoRst.Open strSQL, , adOpenStatic, adLockOptimistic

intTotalClearances = adoRst.RecordCount

' Close recordset
adoRst.Close
Set adoRst = Nothing

End Function


Should return 673 records, but returns 0. I've attempted to re-word the
query in different ways, but it still returns zero. The function is intended
to return a count of all records with a valid security clearance which are
not otherwise REVOKED, SUSPENDED, or DENIED.

Help, please.

 

[Douglas J. Steele]

The wildcard character in ADO is %, not *.