[RESOLVED] How can I find something in a field

[DubweiserTM]

I have an access database with many fields (Client, City, House Model and many others...)

In the "Client" field, there's the Name and the First Name (for example Jack Strap)

I want to find in my database all records with the name Strap in the "Client" field ?

Help me ! Thanks !

[Shuja Ali]

Use Like operator.

Select * from YourTable Where Client Like "*Strap*"

[DubweiserTM]

I'm not really sure if I understand...I have only to do this:

Data1.Recordset.MoveFirst
  
Do
 
    If Data1.Recordset.Fields("Client").Value = "*Strap*" Then
      msgbox "Item found"
    End If
 
    Data1.Recordset.MoveNext
    
    If Data1.Recordset.EOF = True Then
      Exit Do
    End If
 
  Loop

[zubairkhan]

MyRst.open "select * from myTable where firstname like %'Strep' %'"

This will return all records from firstname field where strep is exist .

[Shuja Ali]

I'm not really sure if I understand...I have only to do this:

Data1.Recordset.MoveFirst
  
Do
 
    If Data1.Recordset.Fields("Client").Value = "*Strap*" Then
      msgbox "Item found"
    End If
 
    Data1.Recordset.MoveNext
    
    If Data1.Recordset.EOF = True Then
      Exit Do
    End If
 
  Loop

Seems like you are using Data Controls.

You should ne using the ADODB connection and recordsets in VB and not Data Controls. Data Controls are no good. Search this form and you will get lot of samples on how to use ADO.

[DubweiserTM]

I dont know how to use this, but I found a tutorial on this...

Is it really better than DAO ?

Thanks for your suggestion !

[Hack]

Is it really better than DAO ?

Yes.

In addition, you will find the doing everything through code rather than using bound controls will give you a lot more flexibility and control over what your program does, how it does it and when it does it.

[RobDog888]

Here is a good ADO DB Tutorial - http://www.vbforums.com/showthread.php?t=337051

[DubweiserTM]

Originally Posted by zubairkhan

MyRst.open "select * from myTable where firstname like %'Strep' %'"

I tried to understand something in this line ? Who can help me ?

Sorry but I'm Beginner with ADO...

Thanks !

[DubweiserTM]

I followed the ADO Tutorial but is there nothing about "Select" ?

Is it complicated to use ADO or I'm stupid ?

Help me please ! Thanks !

[RobDog888]

Here is a short example of connection and retrieving data from a table in an Access db.

Option Explicit
'Add a reference to MS ActiveX 2.x Data Objects library
Private Sub Command1_Click()
    On Error GoTo MyError
 
    Dim oCnn As ADODB.Connection
    Dim oRs As ADODB.Recordset
    Dim sSQL As String
 
    Set oCnn = New ADODB.Connection
    oCnn.Connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;User Id=admin;Password=;"
    oCnn.Open
 
    Set oRs = New ADODB.Recordset
    sSQL = "SELECT * FROM Table1;"
    oRs.Open sSQL, oCnn, adOpenKeyset, adLockOptimistic, adCmdText
    If oRs.BOF = False And oRs.EOF = False Then
        Do While oRs.EOF = False
            MsgBox oRs.Fields("Field1").Value
            oRs.MoveNext
        Loop
    Else
        MsgBox "No Records Returned"
    End If
    oRs.Close
    Set oRs = Nothing
    oCnn.Close
    Set oCnn = Nothing
    Exit Sub
MyError:
    MsgBox Err.Number & " - " & Err.Description
End Sub

[DubweiserTM]

For example, Field1 contains Date (ex: 30-12-2005)

How to find in Field1, all Date with 2005 ???

Thanks !

[RobDog888]

Instead of ...
sSQL = "SELECT * FROM Table1;"

You would pass a sql string that will filter the table to the record(s) your looking for.

sSQL = "SELECT * FROM Table1 WHERE Field1 = #30-12-2005#;"

Field1 must be defined in the table as a Date Field Data type or you need a different syntax.

[DubweiserTM]

Am I stupid or SQL is very complicated ???


Thanks for your help RobDog888 but this line returns only this date 30-12-2005

sSQL = "SELECT * FROM Table1 WHERE Field1 = #30-12-2005#;"

I use this statement and all records with 2005 are found:

sSQL = "SELECT * FROM Table1 WHERE Field1 like '%2005%'"

Another questions regarding these two lines:

(a) sSQL = "SELECT * FROM Table1 WHERE Dépositaire like '%home%' ;"
(b) sSQL = "SELECT * FROM Table1 WHERE Dépositaire like '*eric*' ;"

Why (a) returns many records and (b) returns nothing ?

[RobDog888]

Because when using ADO the wild card cahracter is the "%" and not then "*".

Sorry, I thought you wanted a particular date, but if you want a date range, like all 2005, then you can modify the stateement like so.

sSQL = "SELECT * FROM Table1 WHERE YEAR([Field1]) = 2005;"

[AllanM]

i was trying to get a user input, using a text box, i put the txt from the user input into varbale called nameSearch and added the code below Now i get error "-2147217904- No value given for one or more required prams"

So i did a step through on the program and checked the var nameSearch, and it did indeed contain the data.
Im just wondering if its my syntax that is incorrect, or just my code


sSQL = "SELECT * FROM patient WHERE Name = " & nameSearch & " "
'sSQL = "SELECT * FROM patient;"
rs.Open sSQL, cn, adOpenKeyset, adLockOptimistic, adCmdText
If rs.BOF = False And rs.EOF = False Then
Do While rs.EOF = False
List1.AddItem rs.Fields("ID")
rs.MoveNext
Loop
Else
MsgBox "No Records Returned"
End If

when i used the code from the example it outputs all the ID's into the list box

[si_the_geek]

Your syntax isn't quite correct, text data needs to be enclosed in single quotes, and Name is a reserved word (which can cause problems) so needs to be in square brackets - like this:

sSQL = "SELECT * FROM patient WHERE [Name] = [B]'[/B]" & nameSearch & "[B]'[/B] "