By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,825 Members | 970 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,825 IT Pros & Developers. It's quick & easy.

Search Form Doesn't work

P: 1
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

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!!

Jun 6 '07 #1
Share this Question
Share on Google+
1 Reply

Expert 100+
P: 295
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.
Jun 6 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.