Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Function Angst (XP)

    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

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Function Angst (XP)

    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 <!t>[Customers]<!/t> " & _
    "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":
    <hr>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.<hr>

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

    HTH

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Function Angst (XP)

    thanks for the help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •