theinviter
Registered User.
- Local time
- Yesterday, 21:00
- Joined
- Aug 14, 2014
- Messages
- 240
hi all;
I have created a search form to filter the record in sub form based on combo box value ( multiple combo box ) so my issue when i filter the record using one of the combo box name status and other, it dose not show the record where there is blank field in the rows.
this mainly happen in [Escalated_to] field.
this is the code need help please)
Function searchcriteria()
Dim ey_ck1, ey_ck, ey_ck2, ey_date, ey_ck3, ey_ck4, ey_ck5, ey_ck6, ey_ck7, ey_ck8, ey_ck9 As String
Dim task, strcriteria As String
If IsNull(Me.Combo14) Then
ey_ck1 = " [MRN] like '*'"
Else
ey_ck1 = " [MRN]= '" & Me.Combo14 & "'"
End If
If IsNull(Me.Status) Then
ey_ck = " [Complain_Status] like '*'"
Else
ey_ck = " [Complain_Status]= '" & Me.Status & "'"
End If
If IsNull(Me.Combo16) Then
ey_ck2 = " [Escalated_to] like '*'"
Else
ey_ck2 = " [Escalated_to]= '" & Me.Combo16 & "'"
End If
If IsNull(Me.doma) Then
ey_ck3 = " [Domain] like '*'"
Else
ey_ck3 = " [Domain]= '" & Me.doma & "'"
End If
If IsNull(Me.categ_combo) Then
ey_ck4 = " [Category] like '*'"
Else
ey_ck4 = " [Category]= '" & Me.categ_combo & "'"
End If
If IsNull(Me.subcat) Then
ey_ck5 = " [SubCategory] like '*'"
Else
ey_ck5 = " [SubCategory]= '" & Me.subcat & "'"
End If
If IsNull(Me.class) Then
ey_ck6 = " [Classification] like '*'"
Else
ey_ck6 = " [Classification]= '" & Me.class & "'"
End If
If IsNull(Me.Combo145) Then
ey_ck7 = " [user] like '*'"
Else
ey_ck7 = " [user]= '" & Me.Combo145 & "'"
End If
If IsNull(Me.Combo183) Then
ey_ck8 = " [Location] like '*'"
Else
ey_ck8 = " [Location]= '" & Me.Combo183 & "'"
End If
If IsNull(Me.Combo175) Then
ey_ck9 = " [Unit] like '*'"
Else
ey_ck9 = " [Unit]= '" & Me.Combo175 & "'"
End If
If IsNull(Me.Date_from) Or IsNull(Me.Date_to) Then
ey_date = " [Report Date] like '*'"
Else
ey_date = " ([Report Date] >= #" & Me.Date_from & "# And [Report Date] <= #" & Me.Date_to & "#)"
End If
strcriteria = ey_ck1 & "AND" & ey_ck & "AND" & ey_ck2 & "AND" & ey_date & "AND" & ey_ck3 & "AND" & ey_ck4 & "AND" & ey_ck5 & "AND" & ey_ck6 & "AND" & ey_ck7 & "AND" & ey_ck8 & "AND" & ey_ck9
task = "select * from [Patient Complains Query] where " & strcriteria
Me.Patient_Complains_Query.Form.RecordSource = task
Me.Patient_Complains_Query.Form.Requery
I have created a search form to filter the record in sub form based on combo box value ( multiple combo box ) so my issue when i filter the record using one of the combo box name status and other, it dose not show the record where there is blank field in the rows.
this mainly happen in [Escalated_to] field.
this is the code need help please)
Function searchcriteria()
Dim ey_ck1, ey_ck, ey_ck2, ey_date, ey_ck3, ey_ck4, ey_ck5, ey_ck6, ey_ck7, ey_ck8, ey_ck9 As String
Dim task, strcriteria As String
If IsNull(Me.Combo14) Then
ey_ck1 = " [MRN] like '*'"
Else
ey_ck1 = " [MRN]= '" & Me.Combo14 & "'"
End If
If IsNull(Me.Status) Then
ey_ck = " [Complain_Status] like '*'"
Else
ey_ck = " [Complain_Status]= '" & Me.Status & "'"
End If
If IsNull(Me.Combo16) Then
ey_ck2 = " [Escalated_to] like '*'"
Else
ey_ck2 = " [Escalated_to]= '" & Me.Combo16 & "'"
End If
If IsNull(Me.doma) Then
ey_ck3 = " [Domain] like '*'"
Else
ey_ck3 = " [Domain]= '" & Me.doma & "'"
End If
If IsNull(Me.categ_combo) Then
ey_ck4 = " [Category] like '*'"
Else
ey_ck4 = " [Category]= '" & Me.categ_combo & "'"
End If
If IsNull(Me.subcat) Then
ey_ck5 = " [SubCategory] like '*'"
Else
ey_ck5 = " [SubCategory]= '" & Me.subcat & "'"
End If
If IsNull(Me.class) Then
ey_ck6 = " [Classification] like '*'"
Else
ey_ck6 = " [Classification]= '" & Me.class & "'"
End If
If IsNull(Me.Combo145) Then
ey_ck7 = " [user] like '*'"
Else
ey_ck7 = " [user]= '" & Me.Combo145 & "'"
End If
If IsNull(Me.Combo183) Then
ey_ck8 = " [Location] like '*'"
Else
ey_ck8 = " [Location]= '" & Me.Combo183 & "'"
End If
If IsNull(Me.Combo175) Then
ey_ck9 = " [Unit] like '*'"
Else
ey_ck9 = " [Unit]= '" & Me.Combo175 & "'"
End If
If IsNull(Me.Date_from) Or IsNull(Me.Date_to) Then
ey_date = " [Report Date] like '*'"
Else
ey_date = " ([Report Date] >= #" & Me.Date_from & "# And [Report Date] <= #" & Me.Date_to & "#)"
End If
strcriteria = ey_ck1 & "AND" & ey_ck & "AND" & ey_ck2 & "AND" & ey_date & "AND" & ey_ck3 & "AND" & ey_ck4 & "AND" & ey_ck5 & "AND" & ey_ck6 & "AND" & ey_ck7 & "AND" & ey_ck8 & "AND" & ey_ck9
task = "select * from [Patient Complains Query] where " & strcriteria
Me.Patient_Complains_Query.Form.RecordSource = task
Me.Patient_Complains_Query.Form.Requery