ADODB issue with query on date

[webster188]

Hello,

Can anyone help me with this issue which is driving me crazy.

I have a worksheet (not formatted as a table) with a huge amount of data.
I wanted to query for data older than a specific date.

I wrote the following function (sorry about the "select case", I still need to find a better solution for this)

Public Function TotalPIRs(Maand As String)

  Dim EndRow As Integer
  Dim Datum As Date
  
  
  Select Case Maand
    Case "Jan"
        Datum = 43496
    Case "Feb"
        Datum = 43524
    Case "Mar"
        Datum = 43555
    Case "Apr"
        Datum = 43585
  End Select
     
     
  '//Find end of PIRLIST
  EndRow = PIRList.Range("A65536").End(xlUp).Row


  '//Now, search the database for matches on trigger
  '//SQL statement to search the database
  
  strSQL = "SELECT * from [PIRlijst$] WHERE [INITDATE] <= '" & Datum & "'" 


  'now extract data
  closeRS
  OpenDB
        
  rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
    
  '//Check if recordset contains data
  TotalPIRs = rs.RecordCount

INITDATE is the header of the worksheet column which contains the date of initiation. The fields in this column are formatted as dates.


In Cell A5 of worksheet "Analysis", I have the text "Jan", in cell B5 of the same worksheet, i have "=TotalPIRs(A5)"
The formula outputs "#VALUE!" and I have no clue why this is.

Note: I also tried following query
strSQL = "SELECT * from [PIRlijst$] WHERE [INITDATE] <= " & Datum

In that case, the formula output is 0 (zero) although I have plenty of rows with INITDATE older than the searchdate.

 

[John_w]

Try:

    strSQL = "SELECT * from [PIRlijst$] WHERE [INITDATE] <= {ts '" & Format(Datum, "yyyy-mm-dd 00:00:00") & "'}"

 

[webster188]

Worked perfectly. Thanks a lot.