CODEWITHSUNDEEP
excelms-accessvba
Max Bridge
Max Bridge

Reputation: 331

I want get the numbers of records with a criteria from a bd access

Hi I just need to include the criteria = RS somewhere in the field SEG_OPE_REMARQ from the AQ_SEG table in ACCESS

I just need to get the RS part

Everything in the rest of the code work fine

Sub GetNumbersSigeqR()

        'Get Retrait SIGEQ de AQ_SEG_EPEL_XX.mdb.mdb


    Dim mrc As String
    Dim retrait As String

    mrc = Val(Range("D2").Value)

    If Len(mrc) < 2 Then

        mrc = "0" + mrc

    End If


    retrait = "AQ_SEG_EPEL_" + mrc + ".mdb"

    Dim folderPath As String
    folderPath = Application.ActiveWorkbook.Path

    Dim cn As Object
    Dim rs As Object
    Dim strSql As String
    Dim strConnection As String
    Set cn = CreateObject("ADODB.Connection")


    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & folderPath & "\" & retrait
    strSql = "SELECT Count(*) FROM AQ_SEG ;"
    cn.Open strConnection
    Set rs = cn.Execute(strSql)
    Sheets("T9Cp1").Range("G97").Value = rs.Fields(0)
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing


End Sub

Upvotes: 0

Views: 18

Answers (1)

CLR
CLR

Reputation: 12279

I'm still not entirely sure I understand the question (as you mention field containing RS but you also have an object called RS) but based on your response and the original question, the following query for strSQL would count only the rows where the field contains 'RS':

strSql = "SELECT Count(*) FROM AQ_SEG WHERE SEG_OPE_REMARQ LIKE '%RS%';"

Upvotes: 1

Related Questions