VB - Run-time Error '1004' - Application-defined Or Object-defined Error

I have created a CommandButton within Excel and started coding VBA. The idea is to pass parameters to my CommandString so that the user can filter. The 2 parameter fields are of da

Solution 1:

The error says that you are trying to assign the wrong data type.

Variables FromDate and ToDate are declared as Date type, but you are trying to assign texts to them.

If you have dates in this format in your cells: '20100101' and '20150813', you need to convert them to dates before assigning to those variables like below:

Private Sub CommandButton1_Click()
    Dim txtFromDate As String
    Dim txtToDate As String
    Dim FromDate As Date
    Dim ToDate As Date

    txtFromDate = Sheets("Bips Travel Summary").Range("J3").Value
    FromDate = DateSerial(Left(txtFromDate, 4), Mid(txtFromDate, 5, 2), Right(txtFromDate, 2))
    txtToDate = Sheets("Bips Travel Summary").Range("J4").Value
    ToDate = DateSerial(Left(txtToDate, 4), Mid(txtToDate, 5, 2), Right(txtToDate, 2))

    'Pass the Parameters values to the Stored Procedure used in the Data Connection
    With ActiveWorkbook.Connections("192.168.0.3 Timesheets1").OLEDBConnection
        .CommandText = "SELECT ID, Employee,  WT, [Amount Per Kilometer], Currency, SUM([Number (Amount of km)]) AS [Number (Amount of km)], SUM([Total (per record)]) AS [Total (per record)] FROM ( SELECT S.ID ,S.FirstName + ' ' + S.LastName AS [Employee],TS.DateWorked AS [DateTraveled],C.Customer_Name,NULL AS [WT],EC.AA_Rate AS [Amount Per Kilometer],NULL AS [Currency],TS.Travel AS [Number (Amount of km)],TT.TravelDescription,TS.Travel * CONVERT(float, EC.AA_Rate) AS [Total (per record)] FROM [Timesheets].[dbo].[timesheets] TS INNER JOIN [Timesheets].[dbo].[traveltype] TT ON TS.TravelTypeCode = TT.TravelTypeCode INNER JOIN [Timesheets].[dbo].[staff] S ON TS.Staff_Code = S.Staff_Code INNER JOIN [Timesheets].[dbo].[enginecapacity] EC ON TS.EngineCapacityCode = EC.EngineCapacityCode INNER JOIN [Timesheets].[dbo].[customers] C ON TS.Cust_Code = C.Cust_Code WHERE TS.DateWorked BETWEEN '" & FromDate & "' AND '" & ToDate & "') as A GROUP BY ID, Employee, WT, [Amount Per Kilometer], Currency"
        ActiveWorkbook.Connections("192.168.0.3 Timesheets1").Refresh

    End With
End Sub