Recordset SQL MSAccess question rather confused? *RESOLVED*

[Virus00110]

I have a search/find dialog box that pops up when the client/user wants to search the records in the membership table in the database. the client/user can search by member id, home phone, last name, and driver license. the client/user can type in part of a search value followed by an asterisk "*". the value is passed to a function and a SQL statement is created using a LIKE search. This is being performed on an access database. I can get the query to work from within access using the query design view. however, i cannot get the SQL statement to work from within my program when i open a recordset with it. it doesn't fail but it does not return any records. i hope this make sense. here is the SQL statement that i create the SQL is then passed to a class function. any help will be greatly appreciated. thanks in advance.

Public Function FindMember(ByVal sSearchVal As String, ByVal sField As String) As Long
    Dim sSQL    As String
    Dim lCnt    As Long
    
    'Determine if the recordset has been created.
    If rs Is Nothing Then
        Set rs = New ADODB.Recordset
    End If
    
    'Determine if the recordset has been opened.
    If rs.State = adStateOpen Then
        rs.Close
    End If
    
    'Determine if the value being searched for has an astrics
    'in the string.
    If InStr(sSearchVal, "*") Then
        'If there is an astrics perform a SQL LIKE query.
        sSQL = "SELECT * FROM members WHERE " & sField & " LIKE '" & UCase(sSearchVal) & "'"
    Else
        'If there is no astrics perform a normal SQL SELECT query.
        sSQL = "SELECT * FROM members WHERE " & sField & " = '" & UCase(sSearchVal) & "'"
    End If
    
    'Get the member data based on the SQL statement.
    Call GetMemberData(sSQL)
    
    'Get the number of records found if any.
    lCnt = cDB.RecCount(rs)
    If lCnt <= 1 Then
        'If the record count is 1 or less there is no need to use
        'the mover buttons.
        Call DisEnableMemMoverButtons(False, False, False, False, frmMembers)
    Else
        'More then 1 record enable all mover buttons.
        Call DisEnableMemMoverButtons(True, True, True, True, frmMembers)
    End If
    
    FindMember = lCnt
End Function

[apps_tech]

Originally posted by Virus00110

If InStr(sSearchVal, "*") Then
        'If there is an astrics perform a SQL LIKE query.
        sSQL = "SELECT * FROM members WHERE " & sField & " LIKE '" & UCase(sSearchVal) & "'"
    Else
        'If there is no astrics perform a normal SQL SELECT query.
        sSQL = "SELECT * FROM members WHERE " & sField & " = '" & UCase(sSearchVal) & "'"
    End If

My first question is:
Does Instr(sSearchVal,"*") return anything??

If yes, then this might be the solution:
In the first sql where u use Like, try the following sql statement:

sSQL = "SELECT * FROM members WHERE " & sField & " LIKE '%" & UCase(sSearchVal) & "%'"

Let me know if it works!!!!

Enjoy!!!
apps_tech

[MichaelC2468]

You'll also want to replace the "*" in sSearchVal before setting sSQL.

sSearchVal = REPLACE(sSearchVal, "*","")

sSQL = "SELECT * FROM members WHERE " & sField & " LIKE '%" & UCase(sSearchVal) & "%'"

[apps_tech]

Originally posted by MichaelC2468
You'll also want to replace the "*" in sSearchVal before setting sSQL.

sSearchVal = REPLACE(sSearchVal, "*","")

sSQL = "SELECT * FROM members WHERE " & sField & " LIKE '%" & UCase(sSearchVal) & "%'"

Yep...Thats a good catch...

Enjoy!!!
apps_tech

[Virus00110]

thanks guys that did the trick but now heres my next question why do i have to remove the asterisk "*" and put percent simbols around the value?

[Virus00110]

ok i kinda answered my question i tried a few things instead of using the replaces function like this

sSQL = "SELECT * FROM members WHERE " & sField & " Like '%" & UCase(Replace(sSearchVal, "*", "")) & "%'"

i used it like this to refine the search

sSQL = "SELECT * FROM members WHERE " & sField & " Like '" & UCase(Replace(sSearchVal, "*", "%")) & "'"

[apps_tech]

Originally posted by Virus00110
thanks guys that did the trick but now heres my next question why do i have to remove the asterisk "*" and put percent simbols around the value?

U are using "*" as a check value for ur text box. So, it should not be present along with the original value. Else it would try to search for values with "*" in it in the database.

For the reasons of using %:

% is a symbol used along with LIKE operator in SQL Select statements. Please look into the following link for more info:

http://www.engin.umich.edu/caen/oradocs/server.815/a67779/operator.htm#997970

Enjoy!!!!
apps_tech