SQL query based on date on an Excel file database

Question

I use ADO in order to execute SQL queries on an Excel file database and I have a problem while using the following query :

strQuery = "SELECT NUM,CREATION_DATE,CREATOR,STATE,LAST_MODIFICATION_DATE,LAST_MODIFIER,CATEGORY,MODEL_LABEL FROM [MySheet$] WHERE CREATION_DATE<='" + EndDateTextBox.Value + "' AND CREATION_DATE>='" + BeginDateTextBox.Value + "'"

The query is executed but I don't get any result when I enter 2010-01-01 in the BeginDateTextBox and 2016-01-01 in the EndDateTextBox, which is not normal because the dates in my database vary between 2014 and 2015.

Here is a screenshot of the CREATION_DATE column of my database :

PS : I know that using Excel files as a database is not the best practice but I'm told to do so.

Answer 1

You need to convert dates to values, ADO can't recognize format of the cells in sheet, only values applicable. So final query should be something like this:

SELECT * FROM [MySheet$] WHERE CREATION_DATE <= 42370 AND CREATION_DATE >= 40179

you can achieve this using such method:

Sub test()
Dim DtStart as Long
DtStart = Clng(Cdate("2010-01-01"))
Msgbox DtStart
End Sub

so, the final SQL query will be:

strQuery = "SELECT NUM,CREATION_DATE,CREATOR,STATE,LAST_MODIFICATION_DATE,LAST_MODIFIER,CATEGORY,MODEL_LABEL " & _
           "FROM [MySheet$] " & _
           "WHERE CREATION_DATE <= " & CLng(CDate(EndDateTextBox.Value)) & _
           "  AND CREATION_DATE >= " & CLng(CDate(BeginDateTextBox.Value)) 

OR it can be like this:

strQuery = "SELECT NUM,CREATION_DATE,CREATOR,STATE,LAST_MODIFICATION_DATE,LAST_MODIFIER,CATEGORY,MODEL_LABEL " & _
           "FROM [MySheet$] " & _
           "WHERE CREATION_DATE BETWEEN " & _ 
            CLng(CDate(BeginDateTextBox.Value)) & " AND " & CLng(CDate(EndDateTextBox.Value)) 

here the test result:

Answer 2

convert(datetime,CREATION_DATE,103) >= convert(datetime, BeginDateTextBox.Value ,103)

Do this for both the begindatetextbox and enddatetextbox

This should convert both your dates to dd/mm/yyyy format (not 100% sure if it works with dates you might need to convert to string first) so you can compare them in the same format

This is straight up sql(except for the begindatetextbox.value) so it won't work like this you will have to build up your string in code to look like this in the end