J
JK
I know this is a lot to ask but would someone be able to tell me why the
following will not work? Using Access03 I put two date fields on my main form
so that I can use them to sort the data in the subform. I want the user to be
able to insert only one date. If a FROM date is entered, I want everything
after that date. If a TO date is entered, I want everything before that date.
This will not work for some reason. I'm also getting a lot of flickering when
I filter the subform - ugly - not sure how to fix that either - has something
to do with echo - right?
Thx for your help!
JK
Private Sub cmdApplyFilter_Click()
Dim varWhere As Variant, varDateSearch As Variant
Dim rst As DAO.Recordset
On Error GoTo Error_Handler
' Initialize to Null
varWhere = Null
varDateSearch = Null
' First, validate the dates
' If there's something in Contact Date From
If Not IsNothing(Me.txtDateBegin) Then
' First, make sure it's a valid date
If Not IsDate(Me.txtDateBegin) Then
' Nope, warn them and bail
MsgBox "The value in the filter between field is not a valid
date.", vbCritical, "<Error>"
Exit Sub
End If
' Now see if they specified a "to" date
If Not IsNothing(Me.txtDateEnd) Then
' First, make sure it's a valid date
If Not IsDate(Me.txtDateEnd) Then
' Nope, warn them and bail
MsgBox "The value in the filter to field is not a valid
date.", vbCritical, "<Error>"
Exit Sub
End If
' Got two dates, now make sure "to" is >= "from"
If Me.txtDateEnd < Me.txtDateBegin Then
MsgBox "The filter to date must be greater than or equal to
the filter between date.", _
vbCritical, "<Error>"
Exit Sub
End If
End If
Else
' No "from" but did they specify a "to"?
If Not IsNothing(Me.txtDateEnd) Then
' Make sure it's a valid date
If Not IsDate(Me.txtDateEnd) Then
' Nope, warn them and bail
MsgBox "The value in the filter to field is not a valid
date.", vbCritical, "<Error>"
Exit Sub
End If
End If
End If
' Do the filter date(s) next -- this is a toughie
' because we want to end up with one filter on the subquery table
' Check filter from first
If Not IsNothing(Me.txtDateBegin) Then
' .. build the predicate
varDateSearch = "tblWorksheet.CompleteDate >= #" & Me.txtDateBegin &
"#"
End If
' Now do filter To
If Not IsNothing(Me.txtDateEnd) Then
' .. add to the predicate, but add one because CompleteDate includes
' a date AND a time
varDateSearch = (varDateSearch + " AND ") & _
"tblWorksheet.CompleteDate < #" & CDate(Me.txtDateEnd) + 1 & "#"
End If
' Did we build any date filter?
If Not IsNothing(varDateSearch) Then
' OK, add to the overall filter
' Must use a subquery here because the value is in a linking table...
varWhere = (varWhere + " AND ") & _
"[LineID] IN (SELECT LineID FROM tblWorksheet " & _
"WHERE " & varDateSearch & ")"
End If
' Check to see that we built a filter
If IsNothing(varWhere) Then
MsgBox "You must enter at least one date to filter data.",
vbInformation, "Attention!"
Exit Sub
End If
' Open a recordset to see if any rows returned with this filter
Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM tblWorksheet WHERE
" & varWhere)
' See if found none
If rst.RecordCount = 0 Then
MsgBox "No worksheets meet your criteria.", vbInformation,
"Attention!"
' Clean up recordset
rst.Close
Set rst = Nothing
Exit Sub
End If
Me.txtDateBegin.Tag = "Filter Applied"
Me.frmWorksheetEntry_SF.Form.Filter = varWhere
Me.frmWorksheetEntry_SF.Form.FilterOn = True
Me.Refresh
Exit_Procedure:
On Error Resume Next
Exit Sub
Error_Handler:
MsgBox "An error has occurred in this application." & Err & ", " & Error
& vbCrLf & vbCrLf & _
"Please contact your technical support person and report the problem.",
vbExclamation, "Error!"
ErrorLog Me.Name & "_cmdApplyFilter_Click", Err, Error
' Put the focus back in the database window
DoCmd.SelectObject acTable, "ErrorLog", True
Resume Exit_Procedure
End Sub
following will not work? Using Access03 I put two date fields on my main form
so that I can use them to sort the data in the subform. I want the user to be
able to insert only one date. If a FROM date is entered, I want everything
after that date. If a TO date is entered, I want everything before that date.
This will not work for some reason. I'm also getting a lot of flickering when
I filter the subform - ugly - not sure how to fix that either - has something
to do with echo - right?
Thx for your help!
JK
Private Sub cmdApplyFilter_Click()
Dim varWhere As Variant, varDateSearch As Variant
Dim rst As DAO.Recordset
On Error GoTo Error_Handler
' Initialize to Null
varWhere = Null
varDateSearch = Null
' First, validate the dates
' If there's something in Contact Date From
If Not IsNothing(Me.txtDateBegin) Then
' First, make sure it's a valid date
If Not IsDate(Me.txtDateBegin) Then
' Nope, warn them and bail
MsgBox "The value in the filter between field is not a valid
date.", vbCritical, "<Error>"
Exit Sub
End If
' Now see if they specified a "to" date
If Not IsNothing(Me.txtDateEnd) Then
' First, make sure it's a valid date
If Not IsDate(Me.txtDateEnd) Then
' Nope, warn them and bail
MsgBox "The value in the filter to field is not a valid
date.", vbCritical, "<Error>"
Exit Sub
End If
' Got two dates, now make sure "to" is >= "from"
If Me.txtDateEnd < Me.txtDateBegin Then
MsgBox "The filter to date must be greater than or equal to
the filter between date.", _
vbCritical, "<Error>"
Exit Sub
End If
End If
Else
' No "from" but did they specify a "to"?
If Not IsNothing(Me.txtDateEnd) Then
' Make sure it's a valid date
If Not IsDate(Me.txtDateEnd) Then
' Nope, warn them and bail
MsgBox "The value in the filter to field is not a valid
date.", vbCritical, "<Error>"
Exit Sub
End If
End If
End If
' Do the filter date(s) next -- this is a toughie
' because we want to end up with one filter on the subquery table
' Check filter from first
If Not IsNothing(Me.txtDateBegin) Then
' .. build the predicate
varDateSearch = "tblWorksheet.CompleteDate >= #" & Me.txtDateBegin &
"#"
End If
' Now do filter To
If Not IsNothing(Me.txtDateEnd) Then
' .. add to the predicate, but add one because CompleteDate includes
' a date AND a time
varDateSearch = (varDateSearch + " AND ") & _
"tblWorksheet.CompleteDate < #" & CDate(Me.txtDateEnd) + 1 & "#"
End If
' Did we build any date filter?
If Not IsNothing(varDateSearch) Then
' OK, add to the overall filter
' Must use a subquery here because the value is in a linking table...
varWhere = (varWhere + " AND ") & _
"[LineID] IN (SELECT LineID FROM tblWorksheet " & _
"WHERE " & varDateSearch & ")"
End If
' Check to see that we built a filter
If IsNothing(varWhere) Then
MsgBox "You must enter at least one date to filter data.",
vbInformation, "Attention!"
Exit Sub
End If
' Open a recordset to see if any rows returned with this filter
Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM tblWorksheet WHERE
" & varWhere)
' See if found none
If rst.RecordCount = 0 Then
MsgBox "No worksheets meet your criteria.", vbInformation,
"Attention!"
' Clean up recordset
rst.Close
Set rst = Nothing
Exit Sub
End If
Me.txtDateBegin.Tag = "Filter Applied"
Me.frmWorksheetEntry_SF.Form.Filter = varWhere
Me.frmWorksheetEntry_SF.Form.FilterOn = True
Me.Refresh
Exit_Procedure:
On Error Resume Next
Exit Sub
Error_Handler:
MsgBox "An error has occurred in this application." & Err & ", " & Error
& vbCrLf & vbCrLf & _
"Please contact your technical support person and report the problem.",
vbExclamation, "Error!"
ErrorLog Me.Name & "_cmdApplyFilter_Click", Err, Error
' Put the focus back in the database window
DoCmd.SelectObject acTable, "ErrorLog", True
Resume Exit_Procedure
End Sub