OR statement in Report filter

[Guest]

I would like to open a Report using a filter condition. The current filter:

tbl_phpc_dataStore1.case_ref_id=Forms![frm_case_details]!case_ref_id.Value

works fine, but I want to use the same Report to look at a same field when
loaded from a different Form.

The reference to the new filed/form is:

Forms![frm_case_details_data_input]!case_ref_id.Value

I want to create a test condition in the filter statement so that, if the
first form is open, use the case_ref_id. If the first form is closed use the
case_ref_id on the 2nd form. - "frm_case_details_data_input"

Can I create a condition in the filter statement????

This would be the efficient method to creating the same report twice
referring to another form.

Please hlep.

 

[Allen Browne]

Since you are opening the report form your forms, I suggest you remove the
criteria from the query. Instead, put a command button on each form to open
the report. You can then use the WhereCondition of OpenReport, with a
different condition in each form.

This example shows how the event procedure for the first form's button might
look:
Private Sub cmdPreview_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If Not IsNull(Me.case_ref_id) Then
strWhere = "tbl_phpc_dataStore1.case_ref_id = " & Me.case_ref_id
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

The second form would be something like this:
Private Sub cmdPreview_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If CurrentProject.AllForms("frm_case_details").IsLoaded Then
strWhere = "tbl_phpc_dataStore1.case_ref_id = " & _
Forms("frm_case_details").case_ref_id
ElseIf Not IsNull(Me.case_ref_id) Then
strWhere = "tbl_phpc_dataStore1.case_ref_id = " & Me.case_ref_id
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

Notes:
1. Remove the "If Me.Dirty ..." lines if the forms are unbound.

2. Add extra quotes if case_ref_id is a Text field (not a number field):
strWhere = "tbl_phpc_dataStore1.case_ref_id = """ & Me.case_ref_id & """"

3. The code opens the report with all records if the form is at a new record
or the case_ref_id is blank.

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

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

in message
news:[email protected]...

 

[Guest]

Thank you kindly.

Your a superstar.
--
Learning SQL and Access

Since you are opening the report form your forms, I suggest you remove the
criteria from the query. Instead, put a command button on each form to open
the report. You can then use the WhereCondition of OpenReport, with a
different condition in each form.

This example shows how the event procedure for the first form's button might
look:
Private Sub cmdPreview_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If Not IsNull(Me.case_ref_id) Then
strWhere = "tbl_phpc_dataStore1.case_ref_id = " & Me.case_ref_id
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

The second form would be something like this:
Private Sub cmdPreview_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If CurrentProject.AllForms("frm_case_details").IsLoaded Then
strWhere = "tbl_phpc_dataStore1.case_ref_id = " & _
Forms("frm_case_details").case_ref_id
ElseIf Not IsNull(Me.case_ref_id) Then
strWhere = "tbl_phpc_dataStore1.case_ref_id = " & Me.case_ref_id
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

Notes:
1. Remove the "If Me.Dirty ..." lines if the forms are unbound.

2. Add extra quotes if case_ref_id is a Text field (not a number field):
strWhere = "tbl_phpc_dataStore1.case_ref_id = """ & Me.case_ref_id & """"

3. The code opens the report with all records if the form is at a new record
or the case_ref_id is blank.

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

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

in message

I would like to open a Report using a filter condition. The current
filter:

tbl_phpc_dataStore1.case_ref_id=Forms![frm_case_details]!case_ref_id.Value

works fine, but I want to use the same Report to look at a same field when
loaded from a different Form.

The reference to the new filed/form is:

Forms![frm_case_details_data_input]!case_ref_id.Value

I want to create a test condition in the filter statement so that, if the
first form is open, use the case_ref_id. If the first form is closed use
the
case_ref_id on the 2nd form. - "frm_case_details_data_input"

Can I create a condition in the filter statement????

This would be the efficient method to creating the same report twice
referring to another form.