You could use a parameter in the query, e.g.
SELECT *
FROM MyTable
WHERE (NOT Deleted AND [Include deleted rows? Y/N:] = "N")
OR [Include deleted rows? Y/N:] <> "N"
OR [Include deleted rows? Y/N:] IS NULL;
This restricts the result set to those rows where the Deleted column is
FALSE if the user enters N in the parameter prompt. If the user enters Y (or
anything apart from N for that matter) or the user leaves the parameter
prompt blank (NULL) the all rows will be returned.
If you already have a WHERE clause in the query then it gets a little
trickier as you need to get the Boolean logic right, e.g. if the WHERE clause
already said WHERE CITY = "London", then you'd need to use a Boolean AND
after this and parenthesise the above so that it evaluates independently:
SELECT *
FROM MyTable
WHERE City = "London"
AND ((NOT Deleted AND [Include deleted rows? Y/N:] = "N")
OR [Include deleted rows? Y/N:] <> "N"
OR [Include deleted rows? Y/N:] IS NULL);
An alternative approach would be to use a dialogue form to open the report.
On the form you'd have an unbound check box, chkDeletions say, "Include
deleted rows?". Set its default value to False by putting the following code
in the form's Load event procedure:
Me.chkDeletions = False
This gives you two further possible lines of attack. You could leave your
query and report exactly as they are and open the report from a button on the
form (or two buttons, one to preview it, one to print it) with code such as:
Dim strReport As String
Dim strCriteria As String
strReport = "YourReportNameGoesHere"
If Not Me.chkDeletions Then
strCriteria = "Deleted = False"
End If
DoCmd.OpenReport _
ReportName:=strReport, _
View:=acViewPreview, _
WhereCondition:=strCriteria
The other way would be to change the [Include deleted rows? Y/N:] parameter
in the query to a reference to the check box on dialogue form, e.g.
Forms!YourDialogueForm!chkDeletions, in which case the query would go
something like this:
SELECT *
FROM MyTable
WHERE City = "London"
AND ((NOT Deleted AND NOT Forms!YourDialogueForm!chkDeletions)
OR Forms!YourDialogueForm!chkDeletions);
You don't need to test for NULL in this case as the checkbox is either TRUE
or FALSE.
With this second approach you just open the report from a button on the form
without specifying any WhereCondition argument:
Dim strReport As String
strReport = "YourReportNameGoesHere"
DoCmd.OpenReport _
ReportName:=strReport, _
View:=acViewPreview
Using a dialogue form like this you can automatically open it if someone
opens the report from the database window by putting the following code in
the report's Open event procedure:
Const FORMNOTOPEN = 2450
Dim frm As Form
On Error Resume Next
Set frm = Forms!YourDialogueForm
If Err = FORMNOTOPEN Then
DoCmd.OpenForm "frmNameDlg
Cancel = True
Else
If Err <> 0 Then
' unknown error
MsgBox Err.Description, vbExclamation, "Error"
Else
End If
Ken Sheridan
Stafford, England
rob peterson said:
Query has field called deleted. I have a checkbox on a report that uses this
field as a control source. With no criteria set, the report displays a
checkbox - checked or not. That works fine.
Can I somehow put a criteria in the deleted field in the query so it will
ask if I wanted to include deleted items? Otherwise by default, only the
unchecked would display on report.
thanks.
