Search Form Doesn't work

[cherrieccs]

Hi everyone,

I had made a search form in ACCESS with some coding from internet. However, i noted that if some field is blank, it will not able to search any result even if it is found in the database. Would anyone help?

Form layout is under the following link
http://www.geocities.com/richie_ccs/form.bmp

Coding from internet (Allen Browne)

If Not IsNull(Me.Worktype) Then
strWhere = strWhere & "([dbo_W20U999S].[ENDWRKTYPE] = """ & Me.Worktype & """) AND "
End If
If Not IsNull(Me.StatCD) Then
strWhere = strWhere & "([dbo_W20U999S].[ENDSTATCD] = """ & Me.StatCD & """) AND "
End If
If Not IsNull(Me.FunCode) Then
strWhere = strWhere & "([FNCD].[DATAVALUE] = """ & Me.FunCode & """) AND "
End If
If Not IsNull(Me.FromDate) Then
strWhere = strWhere & "([dbo_W20U999S].[CRDATTIM] > """ & Me.FromDate & """) AND "
' strWhere = strWhere & "([EnteredOn] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "

End If

If Not IsNull(Me.ToDate) Then 'Less than the next day.
strWhere = strWhere & "([dbo_W20U999S].[CRDATTIM] < """ & Me.ToDate & """) AND "

End If

lngLen = Len(strWhere) - 5
'MsgBox (lngLen)
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
DoCmd.OpenQuery ("Query1")
End If

Also i wonder if i want to add some more feature like under worktype field, i want to input criteria "test" or "test1", it will come out the result with either test or test1, what can i do?

Thank you very much for help!!

Cherrie

[maxamis4]

Simply put your logic is right. You use the key word "AND". This means that unless all conditions are true no results will show up. You should try maybe using or instead. That might maxamize your output. Also google wild cards, I have found that with search queries they are essentially the best.