Error in Excel VBA with SQL query between two dates

Question

I am using SQL inside an Excel Sheet using the following provider

Set m_Connection = CreateObject("ADODB.Connection")

m_Connection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                  "Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=Yes;"";"

I have two textboxes (from and to) where users type two dates From, To, to select the records corresponding at this date selection

I am facing the following problems:

First of all I tried this code, I was getting all the fields whatever are the dates dates from the sheet, except if I have a blank sheet, the ones where the cells are empty inside the column date are empties, so I wasn't getting an error, but my condition between two dates doesn't work

SELECT [ListName] AS [List name] 
FROM [Database$] 
WHERE CDATE([ClipDate]) BETWEEN #01/09/2017# AND #14/03/2019#;

I tried not to use the BETWEEN, but I got same wrong results (all records except empty ones are selected whatever is the ClipDate)

SELECT [ListName] AS [List name] 
FROM [Database$] 
WHERE CDATE([ClipDate]) >= #01/09/2017# AND CDATE([ClipDate]) <= #14/03/2019#;

I tried the following one, by replacing the # by "", this time I get a good date selection (the ones that I want), but I get an error saying "Invalid Use of Null" if any of the cells at the column Date is empty.

SELECT [ListName] AS [List name] 
FROM [Database$] 
WHERE CDATE([ClipDate]) >= "01/09/2017" AND CDATE([ClipDate]) <= "14/03/2019";

I tried at the end this, and got the same result as previous one , Invalid Use of Null in case of blank cell

SELECT [ListName] AS [List name] 
FROM [Database$] 
WHERE CDATE([ClipDate]) BETWEEN "01/09/2017" AND "11/03/2019";

here is the screen of From, to

In my Excel sheet I have these dates

I think that the problem is that the CDATE is not working on NULL values which is normal and I have to find a way to convert only if not null, but I don't know how. Anyone can help please ?

I can't see where you are converting the dates but If Cell.Value <> vbNullString would do it — Damian
– Damian, Commented Mar 14, 2019 at 16:20
Thanks for trying to help, but I am wondering if you really read my question...I am not looping through cells ! I am making an sql select, that's first...as for conversion, the CDATE is to convert etc....please check back what I wrote, anyway thanks for trying to help — JustGreat
– JustGreat, Commented Mar 14, 2019 at 16:22
Can you show the VBA code block to see how values are passed into SQL query? Also is the entire column of ClipDate formatted as Date? — Parfait
– Parfait, Commented Mar 14, 2019 at 18:01
@Parfait Thanks for trying to help. My data ClipDate column is not formatted), the vb code line that I use to create my condition is the following , MySql = "CDATE([ClipDate]) BETWEEN " & QUO & CDate(Search.txtClipFrom.value) & QUO & " AND " & QUO & CDate(Search.txtClipTo.value) & QUO Before this I add a where and the select of course... and the QUO means a constant containing """" to add quotes and the code that I provided above is the result of the Debug.print of my variable I think what I need in my code is to tell it, don't convert NULL value into dates in select — JustGreat
– JustGreat, Commented Mar 14, 2019 at 18:16
It seems that I got it work by simply removing the CDATE from behind ClipDate, I will keep testing tomorrow to confirm that couldn't test yet on all cases, but these 2 seems to work MySql = "[ClipDate] >= #" & CDate(Search.txtClipFrom.value) & "# AND [ClipDate] <= #" & CDate(Search.txtClipTo.value) & "#" MySql = "[ClipDate] BETWEEN #" & CDate(Search.txtClipFrom.value) & "# AND #" & CDate(Search.txtClipTo.value) & "#" but still would like to know is there a way to say convert to date the clipdate if not null and don't covert if null ? in my sql ? — JustGreat
– JustGreat, Commented Mar 14, 2019 at 18:49

Parfait · Accepted Answer · 2019-03-15 16:39:17Z

3

Consider parameterization which is supported with ADO and allows for binding of VBA values to a prepared SQL query for maintainability and readability and avoidance of type enclosures like pound signs, #.

Sub RunSQL()
On Error GoTo ErrHandle
    Dim m_Connection As Object, cmd As Object, rst As Object
    Dim sqlStr As String
    Const adCmdText = 1, adDate = 7, adParamInput = 1

    ' CONNECTION
    Set m_Connection = CreateObject("ADODB.Connection")

    m_Connection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                      "Data Source=" & ThisWorkbook.FullName & _
                      ";Extended Properties=""Excel 12.0;HDR=Yes;"";"

    ' PREPARED STATEMENT WITH NO DATA BUT PLACE HOLDERS
    sqlStr = "SELECT [ListName] AS [List name]  " _
              & " FROM [Database$]" _
              & " WHERE CDATE([ClipDate]) BETWEEN ? AND ?"

    ' CONFIGURE ADO COMMAND
    Set cmd = CreateObject("ADODB.Command")
    With cmd
        .ActiveConnection = m_Connection
        .CommandText = sqlStr
        .CommandType = adCmdText

        ' BIND VALUES
        .Parameters.Append .CreateParameter("fromdate", adDate, adParamInput, , _
                                            CDate(Search.txtClipFrom.value))
        .Parameters.Append .CreateParameter("todate", adDate, adParamInput, , _
                                            CDate(Search.txtClipTo.value))
    End With

    ' CREATE RECORDSET FROM COMMAND EXECUTION
    Set rst = cmd.Execute
    ThisWorkbook.Worksheets("RESULTS").Range("A2").CopyFromRecordset rst
    rst.Close: m_Connection.Close

    MsgBox "Successfully completed!"

ExitHandle:
    Set cmd = Nothing: Set rst = Nothing: Set m_Connection = Nothing
    Exit Sub

ErrHandle:
    MsgBox Err.Number & " - " & Err.Description, vbCritical, "RUNTIME ERROR"
    Resume ExitHandle
End Sub

edited Mar 15, 2019 at 16:39

answered Mar 14, 2019 at 21:07

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

16 Comments

JustGreat Over a year ago

thanks a lot, I will try it and let you know what it gives but can you please explain to me the idea behind this solution ? and there is something that I am not really understanding, when I use the sql inside my excel, it's the MSAccess sql that I have to use , Or the standard SQL ?

Parfait Over a year ago

The ? is a standard placemarker for parameterization, compliant in any SQL dialect. Let me know if you have issues.

Parfait Over a year ago

The asterisk and percent are wildcard operators. I do not understand they are relevant here. No where here do you use LIKE. Please let me know if this solution does not work for you.

Parfait Over a year ago

No SQL dialect is the same including Access. It uses the ANSI-89 * wildcard for LIKE not the ANSI-92 % but it can with ALIKE. See docs. For params, values in CreateParameter lines are binded in corresponding order to ?. Please test and see.

Parfait Over a year ago

Understood. Access docs, tutorials, forums are abound online. Google/Bing are your friends! Remember SQL is an industry language and different RDBMS's (Oracle, Postgres, SQL Server, etc.) all have their varied dialects. Do note: SQL parameterization is the preferred way and is used beyond VBA in other programming languages (e.g., Java, Python, PHP) interfacing with SQL. If your Excel app connects to an actual database, you never want to concatenate user input values to an SQL string (see the famous Bobby Tables)!

|

Gustav · Accepted Answer · 2019-03-22 11:03:23Z

2

Use CVDate - and the ISO sequence for the values:

SELECT [ListName] AS [List name] 
FROM [Database$] 
WHERE CVDate([ClipDate]) BETWEEN #2017/09/01# AND #2019/03/14#;

Edit. Filter out invalid date strings:

SELECT [ListName] AS [List name] 
FROM [Database$] 
WHERE 
    IsDate([ClipDate]) AND
    CVDate(IIf(IsDate([ClipDate]), [ClipDate], Null)) BETWEEN #2017/09/01# AND #2019/03/14#;

Edit 2: To split the dd/mm/yyyy formatted text dates:

WHERE 
    IsDate([ClipDate]) AND
    DateSerial(Mid([ClipDate], 7, 4), Mid([ClipDate], 4, 2), Mid([ClipDate], 1, 2)) BETWEEN #2017/09/01# AND #2019/03/14#;

edited Mar 22, 2019 at 11:03

answered Mar 14, 2019 at 20:01

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

19 Comments

JustGreat Over a year ago

Thanks I will give it a try and let you know tomorrow, because the workbook is at work and i am now at home. Can you explain to me the difference between the CVDATE and the CDATE that I used ? the CVDATE won't cause error if the field contains null ?

Gustav Over a year ago

Yes. CVDate accepts Null, and returns Null if so, thus returns a Variant that matches the possible values of a DateTime field.

JustGreat Over a year ago

It's true, CVDATE is not giving any error. what is strange is that microsoft suggest not to use it I don't know why, anyway I will pick your answer as used one because it solve the mentioned problem

JustGreat Over a year ago

It seems that I was wrong....I don't have anymore the problem of null, but result is not really right...i put from date 12/12/2013, to date 01/09/2019, and it's not giving me the right results, for example it's not selected the date that are : 12/03/2019..should I reopen a new question or there is a way to get more help here ?

Gustav Over a year ago

Your dates probably represent 2013-12-12 and 2019-01-09, thus will exclude 2019-03-12. Use the ISO sequence as shown in my answer: 2019-09-01 for September 1st.

|

Collectives™ on Stack Overflow

Error in Excel VBA with SQL query between two dates

2 Answers 2

16 Comments

19 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

16 Comments

19 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related