Check Box to display records with completed status only

[Bhupinder Rayat]

Hi,

I have a query whereby a record is lablelled complete if the "Completed
date" field has a date in it, otherwise it is blank.

I want to check box on a form when ticked, to display a report with
completed records only.

Can anyone help?

Thanks,

B/

 

[Allen Browne]

On your form, you have a command button to open the report, and a check box
named (say) chkCompleteOnly. If the check box is checked, you want to open
the report to show only the records that have a date in the [Completed date]
field.

The event procedure for the command button's Click event would look like
this:

Private Sub cmdPreview_Click()
Dim strWhere As String
If Me.[chkCompleteOnly].Value Then
strWhere = "[Completed date] Is Not Null"
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Bhupinder Rayat]

hi Allen,

You're right I do have a command button to open the report, but it doesn't
seem to filter out the records with completed dates.

The report is based on a query and I have taken out the Is Null, Is Not Null
criteria from the query (so the tick box can control it). It is still
showing me all records, checked or unchecked. Here's my code...

Private Sub cmdPreview_Click()
Dim strWhere As String
Dim stDocName As String

stDocName = "qryReportOutstandingByDepartment"

If Me.chkCompleteOnly.Value = True Then
strWhere = "[Completed Date] Is Not Null"
End If
DoCmd.OpenReport stDocName, acViewPreview, , strWhere
End Sub


Am I missing something?

On your form, you have a command button to open the report, and a check box
named (say) chkCompleteOnly. If the check box is checked, you want to open
the report to show only the records that have a date in the [Completed date]
field.

The event procedure for the command button's Click event would look like
this:

Private Sub cmdPreview_Click()
Dim strWhere As String
If Me.[chkCompleteOnly].Value Then
strWhere = "[Completed date] Is Not Null"
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

I have a query whereby a record is lablelled complete if the "Completed
date" field has a date in it, otherwise it is blank.

I want to check box on a form when ticked, to display a report with
completed records only.

 

[Bhupinder Rayat]

Got it working, thanks Allen!

B/

hi Allen,

You're right I do have a command button to open the report, but it doesn't
seem to filter out the records with completed dates.

The report is based on a query and I have taken out the Is Null, Is Not Null
criteria from the query (so the tick box can control it). It is still
showing me all records, checked or unchecked. Here's my code...

Private Sub cmdPreview_Click()
Dim strWhere As String
Dim stDocName As String

stDocName = "qryReportOutstandingByDepartment"

If Me.chkCompleteOnly.Value = True Then
strWhere = "[Completed Date] Is Not Null"
End If
DoCmd.OpenReport stDocName, acViewPreview, , strWhere
End Sub


Am I missing something?

On your form, you have a command button to open the report, and a check box
named (say) chkCompleteOnly. If the check box is checked, you want to open
the report to show only the records that have a date in the [Completed date]
field.

The event procedure for the command button's Click event would look like
this:

Private Sub cmdPreview_Click()
Dim strWhere As String
If Me.[chkCompleteOnly].Value Then
strWhere = "[Completed date] Is Not Null"
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

I have a query whereby a record is lablelled complete if the "Completed
date" field has a date in it, otherwise it is blank.

I want to check box on a form when ticked, to display a report with
completed records only.