Hi,

I?ve written the following function:

Public Function GetFiltered(Optional cs_uri_stem As String, Optional 
cs_uri_query As String, _
        Optional cs_username As String, Optional sc_status As Long, 
Optional s_port As Long) As ADODB.Recordset
    
    Dim pos, length As Integer
    Dim str_query, str_filter As String
    'Recordsetobjekt erstellen
    Dim rs_filtered As ADODB.Recordset
    Set rs_filtered = New ADODB.Recordset
    rs_filtered.CursorLocation = adUseClient
    
    str_query = "select * from [Report]"

    str_filter = ""
    If Not cs_uri_stem = "" Then
        str_filter = str_filter & " [cs-uri-stem] like '*" & cs_uri_stem 
& "*' and "
    End If
    If Not cs_uri_query = "" Then
        str_filter = str_filter & " [cs-uri-query] like '*" & cs_uri_query 
& "*' and "
    End If
    If Not cs_username = "" Then
        str_filter = str_filter & " [cs-username] like '*" & cs_username 
& "*' and "
    End If
    Select Case sc_status
        Case 200302304
            str_filter = str_filter & "[sc-status] = 200 or [sc-status] = 
302 or [sc-status] = 304 and "
    End Select
    If s_port <> 0 Then
        str_filter = str_filter & " [s-port] = " & s_port
    End If
    
    str_filter = Trim(str_filter)
    pos = InStrRev(str_filter, "and") - 1
    length = Len(str_filter)
    If length - pos = 3 Then
        str_filter = Left(str_filter, pos)
    End If
    str_filter = Trim(str_filter)
    
    str_query = str_query & " where " & str_filter
            
    rs_filtered.Open str_query, CurrentProject.Connection, adOpenKeyset, 
adLockOptimistic
    'rs_filtered.Filter = str_filter    
End Function

I've got two problems:
1) I get an error if executing the .open method, although the generated 
query works, if inserted manually in a query.

2) If I only use the "and" or the "where" part, the recordset is empty. If 
I open just the report table and use str_filter as filter, a similar 
situation results: if "and" and "or" used together as filter property, an 
error occurs.

Many thanks in advance and sorry for my bad English!

Henning

Call me crazy, but isn't this the same problem you had earlier? I think
Rand(?) or Richard(?) provided the fix. Use the '%' wild card with ADO
instead of '*'

str_filter & " [cs-uri-stem] like '%" & cs_uri_stem & "%' and "

That should at least solve your empty rescordset problem. I didn't have
any trouble opening the recordset object.



_________________________________________________________________
Tired of spam? Get advanced junk mail protection with MSN 8. 
http://join.msn.com/?page=features/junkmail

> Call me crazy, but isn't this the same problem you had earlier? I think
Rand(?) or Richard(?) provided the fix. Use the '%' wild card with ADO
instead of '*'

str_filter & " [cs-uri-stem] like '%" & cs_uri_stem & "%' and "

That should at least solve your empty rescordset problem. I didn't have
any trouble opening the recordset object.



_________________________________________________________________
Tired of spam? Get advanced junk mail protection with MSN 8. 
http://join.msn.com/?page=features/junkmail

I am sorry, you are right, it is the same problem!