Hi.
I would like to provide a control on a continuous form (like a checkbox) that
displays either all records or records where the Date field is not null. .. . .
When the checkbox is toggled off, it
should display all records.
Place the following code in the module of the form where the check box is
located:
Private Sub chkShowDates_AfterUpdate()
On Error GoTo ErrHandler
Me.Requery
Exit Sub
ErrHandler:
MsgBox "Error in chkShowDates_AfterUpdate( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
End Sub
.. . . where chkShowDates is the name of the check box. Save and compile the
code.
Next, create a new query and open it in SQL View, and paste the following
into it:
SELECT *
FROM tblStuff
WHERE (IIF(Forms!frmStuff!chkShowDates, (IsNULL(SomeDate) = FALSE), 1=1))
ORDER BY ID;
.. . . where tblStuff is the name of the table, frmStuff is the name of the
form that contains the check box, chkShowDates is the name of the check box,
SomeDate is the name of the Date/Time column, and ID is the name of the
column to sort on. Alter the column names, table name, form name, and
control name to suit whatever you have. Save this query.
Set the form's Record Source to this query and save the form. Open the form
in Form View. All the records will show until you check the check box, then
only the records with dates will show. Uncheck the check box and all records
show once again.
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.