Need to generate periodic report when there are two combox already applied in form (1 Viewer)

[ajaythakur]

Hi,

I have developed a database in which I have two comboxes on a form : One "therapeutic area' and second 'phase'. Also, I have time frame(This year, This quarter,This month etc.) filter on same form. With these three filters (two comboxes and one time frame filter) I am generating a report. But, I am not to get all filtered records on my report with three filters applied on form.
Code below:

Private Sub cbotherapeuticarea_AfterUpdate()
Call SearchCriteria
End Sub

Private Sub cbophase_AfterUpdate()
Call SearchCriteria
End Sub


Function SearchCriteria()

Dim therapeuticarea, strphase As String
Dim task, strCriteria As String

If IsNull(Me.cbotherapeuticarea) Then
therapeuticarea = "[Therapeutic area] like '*'"
Else
therapeuticarea = "[Therapeutic area] = '" & Me.cbotherapeuticarea & "'"
End If

If IsNull(Me.cbophase) Then
strphase = "[Phase] like '*'"
Else
strphase = "[Phase] = '" & Me.cbophase & "'"
End If

strCriteria = therapeuticarea & "And" & strphase
task = "Select * from Biosinformation where " & strCriteria
Me.Biosinformation_subform.Form.RecordSource = task
Me.Biosinformation_subform.Form.Requery

End Function


Private Sub Command6_Click()
Me.cbotherapeuticarea = Null
Me.cbophase = Null
Me.Filter = ""
End Sub

Private Sub Command8_Click()

Dim therapeuticarea, strphase As String
Dim strCriteria As String

If IsNull(Me.cbotherapeuticarea) Then
therapeuticarea = "[Therapeutic area] like '*'"
Else
therapeuticarea = "[Therapeutic area] = '" & Me.cbotherapeuticarea & "'"
End If

If IsNull(Me.cbophase) Then
strphase = "[Phase] like '*'"
Else
strphase = "[Phase] = '" & Me.cbophase & "'"
End If

strCriteria = therapeuticarea & "And" & strphase
DoCmd.OpenReport "Biosinformation_search2", acViewPreview, , strCriteria
End Sub



Private Sub Frame15_AfterUpdate()
Dim dDate As Date
Select Case Frame15.Value
Case 1: dDate = "1/1/2016"
Case 2: dDate = "1/1/" & Year(Date)
Case 3: Select Case Month(Date)
Case 1 To 3: dDate = "1/1/" & Year(Date)
Case 4 To 6: dDate = "4/1/" & Year(Date)
Case 7 To 9: dDate = "7/1/" & Year(Date)
Case Else: dDate = "10/1/" & Year(Date)
End Select
Case 4: dDate = Month(Date) & "/1/" & Year(Date)
Case 5: dDate = Date - Weekday(Date) + 2
End Select
Me.Biosinformation_subform.Form.Filter = "[Date]>#" & dDate & "#"
Me.Biosinformation_subform.Form.FilterOn = True
If dDate = "1/1/2016" Then
Me.Biosinformation_subform.Form.FilterOn = False
End If
End Sub

If someone can help, it will be really great!

I have added attachment containing form and report.

 

[arnelgp]

you need space on your And in this expression:

strCriteria = therapeuticarea & "And" & strphase

should be:

strCriteria = therapeuticarea & " And " & strphase

on the next sub, you declared dDate variable as Date, so you must assign date. Use DateSerial for that:

rivate Sub Frame15_AfterUpdate()
Dim dDate As Date
Select Case Frame15.Value
Case 1: dDate = #1/1/2016#
Case 2: dDate = DateSerial(Year(Date()), 1, 1)
Case 3: Select Case Month(Date)
Case 1 To 3: dDate = DateSerial(Year(Date()), 1, 1)
Case 4 To 6: dDate = DateSerial(Year(Date()), 4, 1)
Case 7 To 9: dDate = DateSerial(Year(Date()), 7, 1)
Case Else: dDate = DateSerial(Year(Date()), 10, 1)
End Select
Case 4: dDate = CDate(Month(Date) & "/1/" & Year(Date))
Case 5: dDate = Date - Weekday(Date) + 2
End Select
Me.Biosinformation_subform.Form.Filter = "[Date]>#" & Format(dDate, "mm/dd/yyyy") & "#"
Me.Biosinformation_subform.Form.FilterOn = True
If dDate = #1/1/2016# Then
Me.Biosinformation_subform.Form.FilterOn = False
End If
End Sub

 

[ajaythakur]

Thanks for the help! But This is not what I am looking for.

I am looking for:-

Three filters ( 2 Comboxes and 1 option group), which I have explained in my previous post, are working properly. I can see the filtered record properly in form. But I am not getting these records in report when I click 'view report' button. I think I have problem in date part so how to add that in 'view report' button so that it can be seen in the report.


In the code above cbotherapeuticarea_AfterUpdate(),cbophase_AfterUpdate(),Frame15_AfterUpdate() is workin properly and giving the records in form.
Command8_Click() button of view report is not taking the date part in it.
I have added below again code which I am using to open the report based on three filter criteria. Please help.

Private Sub Command8_Click()

Dim therapeuticarea, strphase As String
Dim strCriteria As String
Dim ddate As Date

If IsNull(Me.cbotherapeuticarea) Then
therapeuticarea = "[Therapeutic area] like '*'"
Else
therapeuticarea = "[Therapeutic area] = '" & Me.cbotherapeuticarea & "'"
End If

If IsNull(Me.cbophase) Then
strphase = "[Phase] like '*'"
Else
strphase = "[Phase] = '" & Me.cbophase & "'"
End If

Select Case Frame15.Value
Case 1: ddate = "1/1/2016"
Case 2: ddate = "1/1/" & Year(Date)
Case 3: Select Case Month(Date)
Case 1 To 3: ddate = "1/1/" & Year(Date)
Case 4 To 6: ddate = "4/1/" & Year(Date)
Case 7 To 9: ddate = "7/1/" & Year(Date)
Case Else: ddate = "10/1/" & Year(Date)
End Select
Case 4: ddate = Month(Date) & "/1/" & Year(Date)
Case 5: ddate = Date - Weekday(Date) + 2
End Select

strCriteria = therapeuticarea & "And" & strphase & "And" & ddate

DoCmd.OpenReport "Biosinformation_search2", acViewPreview, , strCriteria

End Sub