Filtering Out Records From Within the last 4 months (1 Viewer)

[Mtoney76]

Howdy! I've been running an automation process for a Roadmap Database, but have been unable to filter records to hide records from within for months of being deployed. There is a "Deployment Date" field (please forgive the space for I have sinned) which details the date in long time of when the server was originally deployed. In VBA, I have a module which copies data from a Read-Only ODBC Table WIth conditions, adds rows, imports data from another table, and then is supposed to filter out those from within 4 months. its code is here:

Function ExportENGRFiltered4months()
DoCmd.SetWarnings False
DoCmd.Close acTable, "table2", acSaveNo
DoCmd.Close acTable, "dbo_ENGR_Info", acSaveYes
DoCmd.DeleteObject acTable, "Server2"
CurrentProject.Connection.Execute "SELECT * INTO table2 FROM table1 WHERE [Status]= 'Build' AND [Deployment Date] IS NOT NULL"
CurrentProject.Connection.Execute "Alter Table Server2 Add Column `ENGREmail` TEXT(50)"
Dim vSql As Variant
Dim vSqls As Variant
Dim strSql As String
Dim intF As Integer

intF = FreeFile()
Open "C:\sql.txt" For Input As #intF
strSql = Input(LOF(intF), #intF)
Close intF
vSql = Split(strSql, ";")

On Error Resume Next
For Each vSqls In vSql
CurrentDb.Execute vSqls
Next
CurrentProject.Connection.Execute "Alter Table Table2 Add Column `Active` bit"
DoCmd.OpenTable "Table2", acViewNormal, acEdit
'Dim str1 As Date
'Dim strFilter As String
'strFilter = "Status = '" & Build & "'"
'strFilter = strFilter & "And [Deployment Date] <" & "'" & str1 & "'"
'strFilter = strFilter & "And [Deployment Date] Is Not Null"
'DoCmd.SetFilter Wherecondition:=Chr(34) & strFilter & Chr(34)
DoCmd.SetWarnings True
Dim str1 As String
str1 = Format(Date, "Long Time")
str1 = DateAdd("M", -4, str1)
DoCmd.SetFilter WhereCondition:="[Deployment Date]" & "<" & Chr(34) & str1 & Chr(34)
End Function

All of this works, except for the date filter. It'll Deselect all dates, but still show all information. has anyone seen this issue before? and/or if so/if anyone has any advice, please let me know. Thank Y'all in Advance!

 

[pbaldy]

I've never used that method, as I never expose tables or queries to users, only forms and reports. That said, since you want a date value try working with one:

Dim str1 As Date
str1 = Date()
str1 = DateAdd("M", -4, str1)
DoCmd.SetFilter WhereCondition:="[Deployment Date] < #" & str1 & "#"

 

[Minty]

Dates need to have the # symbol around them as delimiters not ' or " . They will also need formatting as mm/dd/yyyy to function reliably. See here for further explaining http://allenbrowne.com/ser-36.html

 

[Mtoney76]

Thank you both for your help, IT WORKS! Words can't describe my appreciation for y'all.
Here's the code for reference
Function ExportENGRFiltered4months()
DoCmd.SetWarnings False
DoCmd.Close acTable, "Table2", acSaveNo
DoCmd.Close acTable, "dbo_ENGR_Info", acSaveYes
DoCmd.DeleteObject acTable, "Table2"
CurrentProject.Connection.Execute "SELECT * INTO Table2 FROM table1 WHERE [Status]= 'Build' AND [Deployment Date] IS NOT NULL"
CurrentProject.Connection.Execute "Alter Table Table2 Add Column `ENGREmail` TEXT(50)"
Dim vSql As Variant
Dim vSqls As Variant
Dim strSql As String
Dim intF As Integer

intF = FreeFile()
Open “C:\sql.txt" For Input As #intF
strSql = Input(LOF(intF), #intF)
Close intF
vSql = Split(strSql, ";")

On Error Resume Next
For Each vSqls In vSql
CurrentDb.Execute vSqls
Next
CurrentProject.Connection.Execute "Alter Table Table2 Add Column `Active` bit"
CurrentProject.Connection.Execute "select convert(varchar(10), cast(`Deployment Date` as date), 101) from Table2"
DoCmd.OpenTable "Table2", acViewNormal, acEdit
'Dim str1 As Date
'Dim strFilter As String
'strFilter = "Status = '" & Build & "'"
'strFilter = strFilter & "And [Deployment Date] <" & "'" & str1 & "'"
'strFilter = strFilter & "And [Deployment Date] Is Not Null"
'DoCmd.SetFilter Wherecondition:=Chr(34) & strFilter & Chr(34)
DoCmd.SetWarnings True
Dim str1 As String, str2 As String
'str2 = Format$(Table2.[Deployment Date], "\#mm\/dd\/yyyy\#")
'str2 = Table2.[Deployment Date]
'str2 = Format(Table2.[Deployment Date], "MM/DD/YYYY")
'str2 = "#" & str2 & "#"
str1 = Format(Date, "Short Date")
str1 = DateAdd("M", -4, str1)
str1 = "#" & str1 & "#"
DoCmd.SetFilter WhereCondition:="[Deployment Date] <" & str1
End Function

 

[pbaldy]

Glad you got it working.