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

 

[Rhino_Dance]

Fazza, I studied this more today and have the query working after I stumbled upon the problem. The data in the MAIN$ sheet used to go down to 60,000 rows. Then I culled it down to only about 7 rows for testing. However the VBA was still evaluating all 60,000 rows when running the query and of course most of these rows were NULL. This was causing the error when the sql/vba tried to turn the null dates into an integer via the Clng function. The simple solution was to delete every empty row on the MAIN$ sheet so that the 'data table' definition would stop after 7 rows.

The only way I found all this out was that I imported the MAIN$ sheet from Excel into Sql Server and I was surprised when it included all the null rows. Then I realized sql / vba must be using the same definition.

Actually, what's vague is how the sql/vba goes about defining the 'data table' on the MAIN$ sheet in the first place. I guess it picks the 'Current Region' of whatever data block it finds and regards that as the data table. But in my case, when data was cleared (but not deleted) from MAIN$ the vba/sql was still looking at the old, larger range all the way down to 60,000 rows. I imagine I will have to put in some vba code that deletes all unused rows on the MAIN$ sheet from now on.

I'm wondering if there's a way to put a range in the 'FROM' clause instead of just referring to the whole worksheet name of MAIN$ ? I tried this but I got an error because in order to reference a named range you have to use "" marks. And the sql/vba interprets the " mark as the end of the command line...

I'm hoping it's clear sailing from here onward as I hope to build more complex sql statements with more parameters to meet my needs. I appreciate all your help on this and can keep you posted on my progress/ findings if you want. Cheers, John

 

[Fazza]

Good work, John

That must be why years ago I favoured normal (non-dynamic) named ranges for data. Dynamic named ranges don't work for these queries.

I've never had a need for quote marks. Just type in the name. So if your named range is MyData then, for example, SELECT * FROM MyData

If you did stick with the worksheet name, and I would recommend you do not, then for a field that must have an entry you could put a clause 'WHERE thatfield Is Not Null' to exclude any worksheet rows that are incorrectly being picked up due to the entire worksheet reference. This works but is obviously a less than ideal solution.

Post again as you wish. New thread will get more eyes, though I may miss it. This thread I will see new posts.

I use ADO often & it is robust, simple enough & powerful. Sound like you've got a good handle on MS Query & its limitations.

Have fun. regards

post script : I often use VBA to update the named ranges. Such as worksheet deactivation code me.range("A1").currentregion.name = "MyData"

 

[Fazza]

rs.Open query, cnStr, adOpenUnspecified, adLockUnspecified

i also think that the cnStr should be a connection object, not the connection string

Can be either, if you have a connection object: though John didn't have a connection object - just the recordset.

If there was a connection object, (1) using its reference would have used that connection, or (2) using a connection string causes a new connection to be used. Refer
http://msdn.microsoft.com/en-us/library/windows/desktop/ms675544(v=vs.85).aspx

I quite often use just a recordset object for one-off queries: not much code needed. And a connection object when there are multiple queries.

ADO is good stuff. Regards

 

[Rhino_Dance]

Fazza, thanks for the additional tips and insight which I'll take into account when dealing with dynamic ranges. Again, I appreciate all your help on this. Cheers, John