Form with search criteria (1 Viewer)

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

After you build your 'strCriteria' string, next line do a 'debug.print strCriteria' and post the result here. I have a sneaking feeling that it may involve not quoting string values. The result of the debug.print should reveal the problem.

With Access vba , you must explicitly DIM your variables --otherwise they become Variant data type.

Dim A as string, B as Integer OR
Dim A as string
Dim B as Integer

I have attached a png showing how Access understands some DIM statements.

Good luck with your project.

thanks guys,
I tried but still same issue, so I came with a solution, not to keep the field blank so that user have to fill the data.

thank you again

Suggest you describe the requirement in simple English to give readers some context. A few examples of your typical search would also be helpful.

Often referenced search info is this by Allen Browne.

Good luck.

theinviter said:

thanks guys,
I tried but still same issue, so I came with a solution, not to keep the field blank so that user have to fill the data.

thank you again

Click to expand...

At best it's a workaround but clearly, you haven't resolved the issue. You may only come across this again when next you create another search form.

Stick with it, post your sample SQL string (from the debug.print) for when the field is blank, as that is the cause of the problem. I'm sure it can be resolved.