Pop up Form Filter

[JimLes]

Hello,

I currently have a pop up form that filters data on another form. The pop up form has 7 fields. The first six work perfectly but the 7th one is a date. Below is my current code. I know you have to surround dates by # but I can't seem to figure out how to tweak the code to accept the 7th field filter as a date. Any ideas?

Private Sub Command11_Click()
Dim strSQL As String, intCounter As Integer
' Build SQL String.
For intCounter = 1 To 7
If Me("Filter" & intCounter) <> "" Then

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & _
" And "
End If
Next

If strSQL <> "" Then
' Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 6))
' Set the Filter property.
Forms![frmSeverance].Filter = strSQL
Forms![frmSeverance].FilterOn = True
End If

End Sub

 

[MajP]

I would loop 1 to 6 since those are all text. Then treat the date textbox separate. Because the loop is surrounding your values with single quotes. On the date textbox you can do

if strSql <> "" and isdate(me.filter7) then
code here
elseif strSql = "" and isdate(me.filter7)
code here
end if

you may also use this to get the proper format.
dim strDate as string
....
strDate = sqlDate(me.filter7)

Function SQLDate(varDate As Variant) As Variant
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

 

[JimLes]

Thanks, that worked great. I tweaked the code slightly to get it to work.....

Private Sub Command11_Click()
Dim strSQL As String, intCounter As Integer
' Build SQL String.
For intCounter = 1 To 6
If Me("Filter" & intCounter) <> "" Then

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & _
" And "
End If

Next

If strSQL <> "" Then
'Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 6))
' Set the Filter property.
Forms![frmSeverance].Filter = strSQL
Forms![frmSeverance].FilterOn = True

ElseIf strSQL = "" And IsDate(Me.Filter7) Then
Forms![frmSeverance].Filter = "[Term Date] = #" & Filter7 & "#"
Forms![frmSeverance].FilterOn = True
End If

End Sub