Excel vba SQL query with datediff

[dsamorano]

I have a SQL database with a date that is text format. I convert to a date on query the use datediff to see if another date is greater than 90 days different. I don't get a error yet it does not remove the SQL entries that are greater than 90 days

sql = "SELECT * FROM PipelineList WHERE DateDiff('d',format(cdate([appdate]),'mm/dd/yyyy'),format(Date(),'mm/dd/yyyy')) < " & 90 & " and [status1] <> '" & "ACTIVE" & " '"

 

[mole999]

if you run print debug sql just after the sql completes (add a break ponit), then take the sql and run it inside server management studio and see if it returns what you get now or acts as expected

 

[dsamorano]

if you run print debug sql just after the sql completes (add a break ponit), then take the sql and run it inside server management studio and see if it returns what you get now or acts as expected

Not sure how to set a breakpoint. But was hoping that the 90 was not being converted to a number. Or something easy like that. Just seems im missing a simple fix

 

[mole999]

in the vba macro, right click a line just after the debug, then look in the immediate window (Ctrl G) and copy the SQL from there

 

[dsamorano]

in the vba macro, right click a line just after the debug, then look in the immediate window (Ctrl G) and copy the SQL from there

Added debug.print SQL immediate window did not show any result.

This code is just to look for records that are past 90 days and delete them.

 

[dsamorano]

This is the code, even tried this but still not deleting records 'sql = "SELECT * FROM PipelineList WHERE [status1] <> '" & "ACTIVE" & " '"

Dim sql As String
Dim rs As DAO.Recordset
Dim CurrentDb As DAO.Database

Set CurrentDb = OpenDatabase(ThisWorkbook.Path & "/DATABASE/" & Left(ThisWorkbook.name, (InStrRev(ThisWorkbook.name, ".", -1, vbTextCompare) - 1)) & ".mdb")

MsgBox "start purge"

'sql = "SELECT * FROM PipelineList WHERE [status1] <> '" & "ACTIVE" & " '"
sql = "SELECT * FROM PipelineList WHERE DateDiff('d',format(cdate([appdate]),'mm/dd/yyyy'),format(Date(),'mm/dd/yyyy')) > " & 90 & " and [status1] <> '" & "ACTIVE" & " '"
Debug.Print sql
Set rs = CurrentDb.OpenRecordset(sql)

If rs.RecordCount < 1 Then

With rs

If Not .BOF And Not .EOF Then

.MoveLast

.MoveFirst

If .Updatable Then
.Delete

End If

End If

.Close

End With

Set rs = Nothing

End If

Exit Sub
End If