VBA error when when running sql against Excel data

[Rhino_Dance]

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

, 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

 

[Fazza]

Sometimes dates can be problematic. Usually I explicitly convert to long data type for criteria testing.

So instead of
WHERE [Date] = " & Range("D4").value
would use
WHERE CLng([Date]) = CLng(" & Range("D4").value & ")"

You'll see too I've explicitly given the .value of the range (& would recommend this is always done in all code, btw). That is Range("D4").value not simply range("D4")

And Date is a bad field name. Just to be sure that wasn't the problem, include an explicit table reference

Such as

SELECT M.

, SUM(M.[Amt]) AS Ttl
FROM [Main$] M
WHERE CLng(M.[Date]) = CLng(" & Range("D4").value & ")"
GROUP BY M.[Code]

 

[Rhino_Dance]

Fazza, Thanks very much for you response. Unfortunately, I am still unable to get the code to work, and I have literally tried 20 or more variations. Here is the exact line I'm using based upon your suggestion:

query = "SELECT M.

, sum(M.[Amt]) as Ttl FROM [Main$] M WHERE CLng(M.[Act_Date]) = CLng(" & Range("D4").Value & ") GROUP BY M.[Code]"

When I run this I get the error:
[I]Wrong number of arguments used with function in query expression 'CLng(M.[Act_Date]) = CLng('.[/I]

Note that I have changed the name of the DATE field to ACT_DATE for good order's sake. Also note that in my original code I used a combination of single quotes and double quotes but you did not. (I still get an error when I add the single quotes though.)  

Now here's an odd thing: When I then edit the code and experiment with other syntax but then circle back so that I have the [I]exact same statement as above[/I] I get a new error which is 'Invalid Use of Null'. This leaves me scratching my head. 

If you are interested in re-creating this on your computer to see if you can get it to work the setup is pretty simple. Just create a simple one record table on a sheet entitled 'Main' that looks like this:


[TABLE="width: 500"]
<tbody>[TR]
[TD][B]Code[/B][/TD]
[TD][B]Act_Date[/B][/TD]
[TD][B]Amt[/B][/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]5/1/13[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]


Then on a second sheet put '5/1/13' in cell D4. Then save the file and put the full file path in the vba code for the variable filename. Then run the macro to see if it returns the value of '100' in cell B9.

By the way, I am running Excel 2013. Thanks for any further suggestions.

 

[Fazza]

I was wrong the other day - hadn't tested it, sorry.

Instead of
WHERE CLng(M.[Act_Date]) = CLng(" & Range("D4").Value & ")

Please try
WHERE CLng(M.[Act_Date]) = " & CLng(Range("D4").Value)

hth

 

[Rhino_Dance]

Fazza. It worked! Thanks very much. I appreciate your help.

 

[Fazza]

 

[Rhino_Dance]

Fazza, I am resurrecting this thread because my efforts to expand this query from a small sample table to using my actual data have lead to an error. Below is the code I am working with now. I'm pulling several more fields than before. I've made sure that all of the fields in the data table match the fields in the query and that the excel ranges 'StartDt1' and 'EndDt1' are valid and both contain a date. The name and path of the file are also correct. I initially had the query on one long line in the vba module but I've transformed it to several lines now. I actually had this working when I first tried it- but now I get the error: 'Invalid Use of Null' and the line that's highlighted is the red one. Any ideas? Thanks!

Sub Pull_ADODB_Compact()

Dim cnStr As String
Dim rs As ADODB.Recordset
Dim query As String

Dim fileName As String

fileName = "C:\Price_Data\P180_Price_Index_Major.xlsm"

cnStr = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & fileName & ";" & _
"Extended Properties=Excel 12.0"


query = "SELECT M.[Item], M.[Prod_Type], M.[Prod_Cat], sum(M.[QtyShp]) as QtyShp,"
query = query & " sum(M.[Rev]) as Rev, sum(M.[Cost]) as Cost,sum(M.[Margin]) as Margin "
query = query & " FROM [Main$] M WHERE CLng(M.[Inv_MO]) >= " & CLng(Range("StartDt1").Value) & ""
query = query & " and CLng(M.[Inv_MO]) <= " & CLng(Range("EndDt1").Value) & ""
query = query & " GROUP BY M.[Item], M.[Prod_Type], M.[Prod_Cat]"
Set rs = New ADODB.Recordset
rs.Open query, cnStr, adOpenUnspecified, adLockUnspecified


'Field Headers goes here
Range("C9").CopyFromRecordset rs

Dim cell As Range, i As Long
With Range("C10").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

 

[Fazza]

hi,

the error occurs at the red line when the query tries to execute

The invalid use of null message suggests there is a null in a field being added - I think a null in another field wouldn't be an issue. untested

so have a look at QtyShp, Rev, Cost & Margin fields for any null entries. [I wonder if the Rev field is one to add?]

BTW, you could try testing on a smaller/simpler dataset to check this. So if you have only 3 records, does the query work or do you get the same error message

if that doesn't help let me know & I'll set up some test data & try it myself

regards

 

[Rhino_Dance]

Fazza, thanks again for your help and your tips. Unfortunately, when I reduce the data to only 3 rows (with no nulls) I still get the error. Cheers, John

 

[Fazza]

OK, John

Two things

1. some sample data (including your headers)

2. to be sure the values pulled from the named ranges are OK, please add a 'debug.print query' line and show me what that gives

regards

POST SCRIPT : before answering the above two points, please check a really simple query like SELECT * FROM table to be sure that everything else is OK.