VB - Run-time error '1004' - Application-d

Question

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 datatype smalldatetime within SQL

Here is my VBA code which executes after I Click the CommandButton :

Private Sub CommandButton1_Click()
    Dim FromDate As Date
    Dim ToDate As Date

    FromDate = Sheets("Bips Travel Summary").Range("J3").Value
    ToDate = Sheets("Bips Travel Summary").Range("J4").Value

    '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

After entering the value for FromDate as 20100101 and ToDate as 20150813 I get an error message which falls over on this script:

FromDate = Sheets("Bips Travel Summary").Range("J3").Value

Error message reads :

Runtime error '13':
Type mismatch

Not sure where to go from here as I am very new to VBA. Could anyone please point me in the right direction to solve this issue?

Answer 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