I have a much more complicated situation, but I bet it will make sense
to you. I have a form setup with each of the fields which are potential
criteria. They are all unbound, but named appropriately. Below is the
code I have associated to the OnClick event of a "Search" button (watch
code wrap):
Private Sub cmdOK_Click()
Dim strSQL As String
Dim intRecords As Integer
strSQL = ""
With Me
If Not IsNull(.txtFirstName) Then strSQL = strSQL & "txtFirstName='"
& .txtFirstName & "' And "
If Not IsNull(.txtLastName) Then strSQL = strSQL & "txtLastName='" &
.txtLastName & "' And "
If Not IsNull(.txtTitle) Then strSQL = strSQL & "txtTitle='" &
.txtTitle & "' And "
If Not IsNull(.txtCompanyName) Then strSQL = strSQL &
"txtCompanyName='" & .txtCompanyName & "' And "
If Not IsNull(.txtAddress1) Then strSQL = strSQL & "txtAddress1='" &
.txtAddress1 & "' And "
If Not IsNull(.txtAddress2) Then strSQL = strSQL & "txtAddress2='" &
.txtAddress2 & "' And "
If Not IsNull(.txtCity) Then strSQL = strSQL & "txtCity='" &
.txtCity & "' And "
If Not IsNull(.txtState) Then strSQL = strSQL & "txtState='" &
.txtState & "' And "
If Not IsNull(.txtZip) Then strSQL = strSQL & "txtZip='" & .txtZip &
"' And "
End With
If strSQL <> "" Then
strSQL = Left(strSQL, Len(strSQL) - 5)
If DCount("lngClientID", "tblClientInfo", strSQL) = 0 Then
MsgBox ("No Records Found")
Exit Sub
Else
intRecords = DCount("lngClientID", "tblClientInfo", strSQL)
End If
With Forms!frmClientInfo
.Filter = strSQL
.FilterOn = True
.Requery
End With
Else
MsgBox "You have not specified any search criteria", , "Search
Parameters"
Exit Sub
End If
DoCmd.Close acForm, Me.Name
End Sub
I hope that helps.
Dawn Crosier
Application Specialist
Microsoft MVP
"Education Lasts a Lifetime"
This message was posted to a newsgroup. Please post replies and
questions to the group so that others can learn as well.
-----Original Message-----
From: [email protected]
[mailto:[EMAIL PROTECTED] On Behalf Of martybevo
Sent: Wednesday, January 25, 2006 2:03 PM
To: [email protected]
Subject: [AccessDevelopers] Multiple search query...
Hi all,
I'm trying to create a search form to basically filter out results. I
have a form with 4 fields, all combo boxes.
Field A - is name
Field B and C - will be used for a date range Field D - is title
I want to be able to filter a query whether 1,2,3 or 4 fields are
populated or not. Meaning, if I select fields 1 and 4 then i will only
see those results.
I'm able to accomplish these queries separately but have no idea on how
to combine them to create an advanced search.
If anybody can assist me or point me in the right direction I would
appreciate it.
Thank you.
Please zip all files prior to uploading to Files section.
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/AccessDevelopers/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
