using sql to get data from excel sheets

Question

I am using SQL to extract data from a worksheet. I have a sheet of sales data ("Sales"). The 1st three columns are SalesDate, Customer and CBand. sample of my sheet

First, I get a list of distinct dates:

wb = ThisWorkbook.Path & "\" & ThisWorkbook.name
Set cn = CreateObject("ADODB.Connection")
With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=" & wb & ";" & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    .Open
End With

sql = "SELECT distinct SalesDate FROM [Sales$] where CBand=3 order by SalesDate desc"
Set RS = cn.Execute(sql)
ct = 0
If Not RS.EOF Then

... etc

I save all the dates thus retrieved in another worksheet.

Then, using one of the dates I have saved, I again query the sales sheet: (note, 'dt' is supplied as a parameter to this sub - eg "08/10/2019" or "05/10/2019" - the same format that the date is stored)

wb = ThisWorkbook.Path & "\" & ThisWorkbook.name
Set cn = CreateObject("ADODB.Connection")
With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=" & wb & ";" & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    .Open
End With
sql = "SELECT distinct Customer FROM [Sales$] where SalesDate = #" & dt & "# and CBand=3"
output = output & sql & vbNewLine
Set RS = cn.Execute(sql)
ct = 0
If Not RS.EOF Then...

The second query will only work for certain dates. For example, using the sample portion of sheet attached, the 5th October returns no rows (the RS.EOF test fails), but the 8th October works fine, and I am able to loop through all the rows associated with that date. I have tried with several dates, most work, but there are a handful that don't, and I can't see why.

I am stumped!

Answer 1

If it comes to dates, you should deal with dates, not with representations of dates. 08/10/2019 is not a date - it's a string that might be seen as a date - but is it 8th of October or is it 10th of August?

If you have real dates in your sheet, they are stored as Double values and only shown to you in a way that you can see them as a date. That depends on the formatting of your cells and also on regional settings of the computer. So 8th of October is stored as number 43746, and if you add 0.5, it's noon of 8th October. You might see it as 08/10/2019, or as 08-Oct-2019 or as 08. Oktober 2019 - possibilities are endless, but the value itself stays the same.

If you want to query dates, you should pass a date parameter to the query. If you pass #" & dt & "#, you are passing a string. if dt is a Date-variable, VBA converts the date to a string and the SQL parser tries to convert it back to a date - not necessarily in the same way. Of course you also don't want to pass 43746 as parameter. You don't mind about how a date is stored, and if you query a different datasource (eg Oracle, SQL Server...), they will store the date in a different format.

The best way to solve this problem is to use ADODB.Parameter. It's a little bit more coding because you cannot pass a ADODB.Parameter to the Execute-method of the Connection-object, you have to involve a ADODB.Command-object, but once you are used to it, you will enjoy the benefits: You no longer have to take care about formatting, don't need to put a string-parameter into quotes and so on. Plus, it prevents SQL injection.

Have a look to the following code (as you can see, I prefer to use early binding, but of course this works also when you switch to late binding)

Dim cn As ADODB.Connection, cmd As ADODB.Command, param As ADODB.Parameter
Set cn = New ADODB.Connection

With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    .Open
End With

Dim sql As String
sql = "SELECT * FROM [Sheet1$] where SalesDate = ?"   ' The ? is a placeholder for a parameter
' Create command
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cn
cmd.CommandText = sql
' Create a parameter of type Date and pass the date value
Set param = cmd.CreateParameter("p1", adDate, adParamInput, , dt)
cmd.Parameters.Append param

Dim rs As ADODB.Recordset
Set rs = cmd.Execute

With this, the query runs with a real Date (there are other parameter types for numbers, Strings etc).

Now, there is another aspect when it comes to SQL queries involving dates: A date can contain a time part, which is stored as the fraction part of the double. If you're searching for a date and you want to include date values from the whole day, you could change your code to

sql = "SELECT * FROM [Sheet1$] where SalesDate >= ? and SalesDate < ?"
' Now we have to provide two parameters:
Set param = cmd.CreateParameter("p1", adDate, adParamInput, , dt)
cmd.Parameters.Append param
' Second parameter is the next day - a little lazy, you can deal with DateAdd instead
Set param = cmd.CreateParameter("p2", adDate, adParamInput, , dt+1)
cmd.Parameters.Append param