form criteria for dates that are not null

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.

It's easy enough to put 'Is Not Null' in the criteria line of the query, but
can I point to some control on a form that will return "Is Not Null" to the
query criteria line when necessary? When the checkbox is toggled off, it
should display all records.

Thanks in advance.
 
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.
Click to expand...

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.
 
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.

It's easy enough to put 'Is Not Null' in the criteria line of the query, but
can I point to some control on a form that will return "Is Not Null" to the
query criteria line when necessary? When the checkbox is toggled off, it
should display all records.

Thanks in advance.
Click to expand...


No need to modify the query at all.
Place your unbound checkbox control in the form Header (not the detail
section).
Code the Check Box AferUpdate event:

If Me![CheckBoxName] = 0 Then
Me.FilterOn = False
Else
Me.Filter = "[ADate] is not null"
Me.FilterOn = True
End If
 
Thanks; I already had something similar, but using the Filter method is
causing me problems on other fronts. I was hoping to get all of the criteria
into a query.



fredg said:
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.

It's easy enough to put 'Is Not Null' in the criteria line of the query, but
can I point to some control on a form that will return "Is Not Null" to the
query criteria line when necessary? When the checkbox is toggled off, it
should display all records.

Thanks in advance.
Click to expand...


No need to modify the query at all.
Place your unbound checkbox control in the form Header (not the detail
section).
Code the Check Box AferUpdate event:

If Me![CheckBoxName] = 0 Then
Me.FilterOn = False
Else
Me.Filter = "[ADate] is not null"
Me.FilterOn = True
End If
Click to expand...
 
Thanks;

So far, I haven't gotten it to work. For example, if I just use the middle
part (the true part) of the iif statement by itself for criteria, it returns
all records.

Was that intended to be a statement that returns non null records in the
Somedate field?
 
Back
Top