VBA to Query Access

[AndrewKent]

Hi there,

I seem to be having a little bit of a problem with my code. I am able to return a COUNT of the number of records from tblIndex where Created_By equals Andrew Kent, however I want to modify my code so that it will count anything beginning with A.

Better yet, modified so that it counts anything beginning with the value or a cell. Please see below...

Sub CountRecord()
'   =============================================================================================
'
'   =============================================================================================
    StartTimer ' This code is required for testing purposes only
    Dim DBName, DBLocation, FilePath As String
    Dim DBConnection As ADODB.Connection
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    Set DBConnection = New ADODB.Connection
    DBName = Worksheets("Calculation Matrix").Range("CalculationMatrix_DatabaseName").Value
    DBLocation = Worksheets("Calculation Matrix").Range("CalculationMatrix_DatabaseLocation").Value
    FilePath = DBLocation & DBName
    With DBConnection
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open FilePath
    End With
    
    Call CountIndex2(DBConnection)
    DBConnection.Close
    Set DBConnection = Nothing
    
    Worksheets("Index").Activate
    Range("A1").Select
    
    EndTimer ' This code is required for testing purposes only
End Sub
 
Sub CountIndex2(DBConnection As ADODB.Connection)
'   =============================================================================================
'   =============================================================================================
    Dim DBRecordset As ADODB.Recordset
    Dim Query As String
    Dim MagicNumber As Long
    
    Query = "SELECT  tblIndex.Created_By FROM tblIndex WHERE tblIndex.Created_By Like 'Andrew Kent'"
    '" & Worksheets("Calculation Matrix").Range("CalculationMatrix_Search").Value
    Set DBRecordset = New ADODB.Recordset
    DBRecordset.CursorLocation = adUseServer
    DBRecordset.Open Source:=Query, ActiveConnection:=DBConnection, CursorType:=adOpenStatic, LockType:=adLockOptimistic, Options:=adCmdText
    MagicNumber = DBRecordset.RecordCount
    
    MsgBox (MagicNumber)
    DBRecordset.Close
    Set DBRecordset = Nothing
End Sub

Cheers,

Andy

[Bob Phillips]

[vba]

Query = "SELECT tblIndex.Created_By FROM tblIndex WHERE tblIndex.Created_By Like '" Range("A1").Value & "%'"
[/vba]

[AndrewKent]

perfect! thanks!