Rhino_Dance
Board Regular
- Joined
- Jul 23, 2008
- Messages
- 74
I have an Excel file with a table of data on Sheet1. The table of data has 3 fields which are Code (a Text field) , Date (a Date field) and Amt (a Number field). I'm using vba with sql code to query that table and return the results of the query to a different sheet in the same file. So just to reiterate, Excel is my data source. I've done my research and largely worked this code out.
I'm also plugging a date parameter into the WHERE CLAUSE of the vba/sql code and this date is drawn from cell D4. This way, the user can change the date in cell D4 and this will change the criteria in the WHERE clause of the sql query to give them the results they want.
However I am getting an error that reads 'Data type mismatch in criteria expression' when I run the below code, so there must be something wrong with the specific syntax I'm using to plug in the date from cell D4.
Note that I can get the code and parameter to work if I make the CODE field the parameter with the query definition line of :
query = "SELECT
I'm also plugging a date parameter into the WHERE CLAUSE of the vba/sql code and this date is drawn from cell D4. This way, the user can change the date in cell D4 and this will change the criteria in the WHERE clause of the sql query to give them the results they want.
However I am getting an error that reads 'Data type mismatch in criteria expression' when I run the below code, so there must be something wrong with the specific syntax I'm using to plug in the date from cell D4.
Note that I can get the code and parameter to work if I make the CODE field the parameter with the query definition line of :
query = "SELECT
Code:
, sum([Amt]) as Ttl FROM [Main$] WHERE [Code] = '" & Range("D4").Value & "' group by [Code]"
Can anyone tell me how I can make the date field work as the parameter? The line in question is highlighted in [COLOR=#ff0000]red[/COLOR]
Thanks & Happy New Year
Sub Pull_Data_from_Excel_with_ADODB()
Dim cnStr As String
Dim rs As ADODB.Recordset
Dim query As String
Dim fileName As String
fileName = "C:\Price_Data\P180_Main_Dec23a.xlsm"
cnStr = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & fileName & ";" & _
"Extended Properties=Excel 12.0"
[COLOR=#ff0000]query = "SELECT [Code], sum([Amt]) as Ttl FROM [Main$] WHERE [Date] = '" & Range("D4") & "' group by [Code]"[/COLOR]
Set rs = New ADODB.Recordset
rs.Open query, cnStr, adOpenUnspecified, adLockUnspecified
'Field Headers goes here
Range("B9").CopyFromRecordset rs
Dim cell As Range, i As Long
With Range("B8").CurrentRegion
For i = 0 To rs.Fields.Count - 1
.Cells(1, i + 1).Value = rs.Fields(i).Name
Next i
.EntireColumn.AutoFit
End With
End Sub
