CODEWITHSUNDEEP
ms-accessfindvba
Paolo Bernasconi
Paolo Bernasconi

Reputation: 2030

How to search a field in a table in Access

Using VBA, how can I search for a text string, for example "CHIR", in a table called "ServiceYES", in the field "Service".

After that, I would like to save the neighboring field for all the rows that "CHIR" exists in the table "ServicesYES". The "ServiceYES" table is below:

ServiceYES table

I basically, want to find all the "CHIR" in "Service" column and then save the names which are on the left of the CHIR, eg "FRANKL_L", "SANTIA_D" as an array.

Thanks for all your help in advance.

Upvotes: 2

Views: 31204

Answers (3)

Chris
Chris

Reputation: 6780

Paolo,

Here is something I threw together in a few minutes. You can add it to the VBA editor in a module. It uses a trick to get the RecordCount property to behave properly. As for returing the array, you can update the function and create a calling routine. If you need that bit of code, just post a comment.

Thanks!

Option Compare Database

Function QueryServiceYES()
    Dim db As Database
    Dim saveItems() As String

    Set db = CurrentDb

    Dim rs As DAO.Recordset
    Set rs = db.OpenRecordset("SELECT Code_Perso, Service, Favorites " & _
                                "FROM ServiceYES " & _
                                "WHERE Service = 'CHIR'")

    'bug in recordset, MoveFirst, then MoveLast forces correct invalid "RecordCount"
    rs.MoveLast
    rs.MoveFirst

    ReDim Preserve saveItems(rs.RecordCount) As String

    For i = 0 To rs.RecordCount - 1
        saveItems(i) = rs.Fields("Code_Perso")

        rs.MoveNext
    Next i

    'print them out
    For i = 0 To UBound(saveItems) - 1
        Debug.Print saveItems(i)
    Next i

    rs.Close
    Set rs = Nothing

    db.Close
    Set db = Nothing
End Function

Upvotes: 1

HansUp
HansUp

Reputation: 97131

Start by creating a SELECT query.

SELECT Code_Perso
FROM ServicesYES
WHERE Service = 'CHIR';

Use SELECT DISTINCT Code_Perso if you want only the unique values.

Add ORDER BY Code_Perso if you care to have them sorted alphabetically.

Once you have a satisfactory query, open a DAO recordset based on that query, and loop through the Code_Perso values it returns.

You don't need to load them directly into your final array. It might be easier to add them to a comma-separated string. Afterward you can use the Split() function (assuming you have Access version >= 2000) to create your array.

Here's sample code to get you started. It's mostly standard boiler-plate, but it might actually work ... once you give it "yourquery". 

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strItems As String
Dim varItems As Variant
Set db = CurrentDb
Set rs = db.OpenRecordset("yourquery", dbOpenSnapshot)
With rs
    Do While Not .EOF
        strItems = strItems & "," & !Code_Perso
        .MoveNext
    Loop
    .Close
End With
If Len(strItems) > 0 Then
    ' discard leading comma '
    strItems = Mid(strItems, 2)
    varItems = Split(strItems, ",")
Else
    MsgBox "Oops.  No matching rows found."
End If
Set rs = Nothing
Set db = Nothing

Upvotes: 3

PowerUser
PowerUser

Reputation: 11801

I tested this and it seems to work. This function will pull all records where ServiceYes='CHIR' and dump the Code_Person value into an array which it will return:

Function x() As String()
    Dim rst As Recordset
    Set rst = CurrentDb.OpenRecordset( _
         "Select * from ServiceYES where Service='CHIR'")

    Dim Arr() As String
    Dim i As Integer

    While rst.EOF = False
         ReDim Preserve Arr(i)
         Arr(i) = rst.Fields("Code_Person")
         i = i + 1
    rst.MoveNext
    Wend
    x = Arr
End Function

Sample Usage:

Debug.Print x()(0)

Upvotes: 2

Related Questions