Function Angst (XP)

Topic

New Reply

I have the following function written. When I run the query that results from the SQL string (which I print in the debug window) in a query window I get rows returned. However the function always returns no rows found. Any ideas?
Public Function fCheckTZehut(txtZehut As String) As Boolean
Dim txtSql1 As String
Dim txtSql2 As String
Dim txtSql3 As String
Dim txtsqlFull As String
Dim bResult As Boolean
Dim rst As ADODB.Recordset
Dim cnn As Connection

Set cnn = CurrentProject.Connection
txtSql1 = “SELECT tblKever.intZehut FROM tblKever WHERE (((tblKever.intZehut) Like “”*”
txtSql2 = txtZehut
txtSql3 = “*””));”
txtsqlFull = txtSql1 & txtSql2 & txtSql3

Debug.Print txtsqlFull

Set rst = New ADODB.Recordset
rst.CursorType = adOpenStatic
rst.ActiveConnection = cnn
rst.Open txtsqlFull

If Not rst.BOF And Not rst.EOF Then

Debug.Print rst.RecordCount
bResult = True
Else
bResult = False
Debug.Print rst.RecordCount
End If

fCheckTZehut = bResult

Thanks

Reply | Quote

Replies

If opening ADO Recordset recommend replace asterisk (“*”) with percent sign (“%”). If using ADO, you have to use ANSI 92 wildcard characters rather than the Jet-specific wildcard characters used in Access queries. This is because the Microsoft Jet OLEDB provider (which is the provider ADO is using in this case) recognizes only ANSI 92 wildcard characters. For more info on Jet vs ANSI-92 wildcards look in Access Help under “About using wildcard characters” topic.

Note: if opening recordset in current database (ie, CurrentProject) based on the current connection, there’s no need to create a Connection object. Example:

Public Function TestGetRecordCountADO() As Long
On Error GoTo Err_Handler

Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strMsg As String

strSQL = “SELECT CustomerID FROM [Customers] ” & _
“WHERE CustomerID Like ‘%’;”
Set rst = New ADODB.Recordset
rst.Open strSQL, CurrentProject.Connection, adOpenStatic
TestGetRecordCountADO = rst.RecordCount

‘ For Test Purposes:
Debug.Print “Record Count: ” & rst.RecordCount

Exit_Sub:
If Not rst Is Nothing Then rst.Close
Set rst = Nothing
Exit Function

Err_Handler:
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
Beep
MsgBox strMsg, vbExclamation, “ADO RECORD COUNT ERROR”
Resume Exit_Sub

End Function

As you probably know, if opening ADO recordset, whether RecordCount returns actual number of records, or -1 or 0, depends on the cursor type. As noted in “Help”:
[indent]

---

The cursor type of the Recordset object affects whether the number of records can be determined. The RecordCount property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and either -1 or the actual count for a dynamic cursor, depending on the data source.

---

[/indent]

Since you specified adOpenStatic as CursorType in your sub this should not be an issue if you modify wildcard character.

HTH

Reply | Quote

thanks for the help

Reply | Quote