Convertion failed when converting DateTime to character stringto string not converting

[jwp]

Here is my simple SQL query in Excel 2003

Sub Date_Range()

Dim Start_date As String, strH As String


Start_date = Application.InputBox(Prompt:="Start date?", Type:=2)


strH = "Select MATTER_NO, DETAIL1, DATE_OPENED" _
& " From Matter Where DATE_OPENED >= 'Start_date' " _
& " Group by MATTER_NO, DETAIL1, DATE_OPENED Order by DATE_OPENED ASC"

With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Worksheets("Sheet5").Range("A2"), Sql:=strH)
.Refresh
End With
'
End Sub

Can someone tell me how to overcome this error when I am writing sql queries with VB in Excel.

Thanks

 

[Andrew Poulsom]

Welcome to MrExcel.

What would strH look like with an explict date?

 

[baitmaster]

welcome to the board. Please can you be more specific when explaining your problem, e.g. what type or error are you getting?

However, in this case, I note you declare the variable Start_date, but then use it as 'Start_date' within a text string. Try changing your code to the following:

strH = "Select MATTER_NO, DETAIL1, DATE_OPENED" _
& " From Matter Where DATE_OPENED >= " & Start_date & _
" Group by MATTER_NO, DETAIL1, DATE_OPENED Order by DATE_OPENED ASC"

 

[jwp]

Thank you for the replies I found the problem the extra ' around the input parameter and declaring it as a string has solved my problem.

What a relief now back to actually generating these reports thanks guys!



strH = "Select MATTER_NO, DETAIL1, DATE_OPENED" _
& " From Matter Where DATE_OPENED >= ' " & Start_date & _
" ' Group by MATTER_NO, DETAIL1, DATE_OPENED Order by DATE_OPENED ASC"