Multiple Filter Search Form - AND/OR?? 2

[Elvis72]

Same Search Form as the previous post but it just hit me that either you have all the criteria given or you have nothing.

So how would I change this to an AND/OR Search?

Private Sub Command12_Click()

Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"


If Not IsNull(Me.txtfirstname) Then
strWhere = strWhere & "([First Name] Like ""*" & Me.txtfirstname & "*"") AND "
End If


If Not IsNull(Me.txtlastname) Then
strWhere = strWhere & "([Last Name] Like ""*" & Me.txtlastname & "*"") AND "
End If

If Not IsNull(Me.txtexperience) Then
strWhere = strWhere & "([Experience] Like ""*" & Me.txtexperience & "*"") AND "
End If

If Not IsNull(Me.txtexperience1) Then
strWhere = strWhere & "([Experience] Like ""*" & Me.txtexperience1 & "*"") AND "
End If
If Not IsNull(Me.txtexperience2) Then
strWhere = strWhere & "([Experience] Like ""*" & Me.txtexperience2 & "*"") AND "
End If
If Not IsNull(Me.txtexperience3) Then
strWhere = strWhere & "([Experience] Like ""*" & Me.txtexperience3 & "*"") AND "
End If

If Me.ckplantservices = -1 Then
strWhere = strWhere & "([Plant Services] = True) AND "
ElseIf Me.ckplantservices = 0 Then
strWhere = strWhere & "([Plant Services] = False) AND "
End If

If Not IsNull(Me.txtyrsexp) Then
strWhere = strWhere & "([Years Experience]>='" & Me.txtyrsexp & "') AND "
End If
If Not IsNull(Me.txtyrsexpend) Then
strWhere = strWhere & "([Years Experience]<='" & Me.txtyrsexpend & "') AND "
End If

If Not IsNull(Me.txtplanttype) Then
strWhere = strWhere & "([Plant Type] Like ""*" & Me.txtplanttype & "*"") AND "
End If

If Not IsNull(Me.txtplanttype1) Then
strWhere = strWhere & "([Plant Type] Like ""*" & Me.txtplanttype1 & "*"") AND "
End If

If Not IsNull(Me.txtplanttype2) Then
strWhere = strWhere & "([Plant Type] Like ""*" & Me.txtplanttype2 & "*"") AND "
End If

If Not IsNull(Me.txtplanttype3) Then
strWhere = strWhere & "([Plant Type] Like ""*" & Me.txtplanttype3 & "*"") AND "
End If

If Not IsNull(Me.memoresume) Then
strWhere = strWhere & "([Resume Memo] Like ""*" & Me.memoresume & "*"") AND "
End If


lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

 

[MajP]

So it is still chopping off 1 too many characters
([Resume Memo] Like "*proposal*"
not
OR ([Resume Memo] Like "*proposal*")

I guess I am doing my math wrong and
it should go back to -1

Try that and
post the string one more time

 

[Elvis72]

This is with - 1

([Experience] Like "*Proposal*") OR ([Resume Memo] Like "*proposal*"

This is with - 2

([Experience] Like "*Proposal*") OR ([Resume Memo] Like "*proposal*"

I'm not seeing the difference?

And neither of them do anything with the form?

 

[MajP]

yes until we get the string correct, nothing will happen. I would think you would actually get an error.

can you post everything you have now that is below this line of code

 If Not IsNull(Me.memoresume) Then
        strWhere = strWhere & "([Resume Memo] Like ""*" & Me.memoresume & "*"") AND "
    End If

 

[Elvis72]

Here it is!~

lngLen = Len(strWhere) - Len(Trim(andOr)) - 2
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere

'Me.Filter = strWhere
'Me.FilterOn = True
End If
End Sub

 

[PHV]

Reread my post stamped 14 Oct 08 11:31

 

[Elvis72]

OK, I have tried - 1 and -2 respectively...and it does not change???

 

[Elvis72]

OK...here is a new thing...

If I select "AND" or "OR" from the combobox it always says "OR" in the Filter...

Private Sub Combo53_AfterUpdate()
andOr = Combo53
End Sub

([Experience] Like "*Proposal*") OR ([Resume Memo] Like "*proposal*

 

[MajP]

Elvis,
If you want. Post this on a free file sharing site like 4shared, and I will take a look. I can not see the problem.

 

[Elvis72]

Here is the link to the file!~

http://www.4shared.com/file/6702416...ruction_Services_Database_-_Working_Copy.html

 

[PHV]

Your code works for me (even with -2)

 

[MajP]

It seems to work fine, not sure how you are testing it. For test purposes I added a message box and just uncommented the filter. The message box shows an appropriate filter and it appears to filter correctly.

Else
strWhere = Left$(strWhere, lngLen)
MsgBox strWhere

Me.Filter = strWhere
Me.FilterOn = True


Also on the combo set the "Default Value" to "AND".

 

[Elvis72]

I'm trying not to cry at this point.

When I open the form and select AND from the ComboBox, then I type Proposal in the Resume Memo field and Proposal in the Experience dropdown, then I hit Apply Filter...NOTHING...

This is also after I put the Default Value at AND...

I will try the message box from above tomorrow morning...I'm going to go get a HUGE margarita...

Thanks soooo much for all of your help!~

 

[MajP]

Did you remember to take the comments off of the following?
'Me.Filter = strWhere
'Me.FilterOn = True
to make
Me.Filter = strWhere
Me.FilterOn = True

Messages boxes are also helpful debug tools, that show the code is executing properly.

 

[Elvis72]

MajP.....Thank you so much for the reminder...it works!~

You've made my week!~

Thank you sooo very much!~

 

[Elvis72]

OK...I might have stuck my foot in my mouth...

The form filters...but not exactly right?

I added a record that had no data in experience or resume memo.

If I type in Proposal in the Resume Memo field and I type in Safety in the Experience field, and I select OR for the filter.

It shows me all the records, when it should only show me 2?

That and it is not bringing back all the records when I hit clear filter. And I don't see what I have changed to make that stop?

 

[Elvis72]

OK...I'm a dork...I figured it out!~

 

[Elvis72]

But I am having the issue that after I hit clear filter the ComboBox is not resetting with the default AND value and the filter is not working.

 

[MajP]

1) In the event for the clear filter just reset the value
combo53.value = "AND"
2)As I suggested either need to ensure that combo53 always has a default value in it or you can pop up the message at the beginning of your routine

if isnull(combo53.value) then
msgbox "Select an AND filter or OR filter"
exit sub
else
do the rest of your code
end if

 

[Elvis72]

I'm not sure where to reset the value?

 

[MajP]

I would think if they were doing an OR search or an AND search you would leave combo53 as it was. So you clear all the controls except for combo53

So I put an if check.

 For Each ctl In Me.Section(acHeader).Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox
   
            [b]If Not ctl.Name = "Combo53" Then
              ctl.Value = Null
            End If [/b]
    
       Case acCheckBox
            ctl.Value = False
        End Select
    Next
     Me.Refresh
    'me.combo53.value = "AND"  
    
    'Remove the form's filter.[
    Me.FilterOn = False

If that is not the functionality you want then delete the if check and uncomment the line
me.combo53.value = "AND"

Also you need a Me.refresh. If you notice you have to hit the clear button twice.