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

 

[PHV]

And the problem is ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

I assume you want to give the user the ability to pick either

- Return records that meet ALL criteria
- Return records that meet ANY criteria

1)Set a public variable in the form
public andOr as string
2)Have some control on the form to make the form into an "And" form or a "Or" form. Sets the value of "andOr"
to "and" or "or".

3)Then change your code throughout to something like.

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

 

[Elvis72]

Ideally I would love to be able to do that...but I'm thinking it would be over my head Access wise.

When you say public variable, I have no idea what that means.

Nor do I know how I would go about setting a control to tell it to look for and versus or.

Is there a walk through somewhere that could help?

 

[MajP]

If you wrote the above code then this would be very simple for you. A variable declared outside of a procedure at the top of your module is public, it can be used by all procedures in the module. Variable declared in a procedure are local and can only get used in the procedure. So your form's module looks like:

Option Compare Database

Public someVariable as variableType
public someOtherVariable as variableType
...

your procedures

Then you can put any type of control on your form to set the value. Lets say it is a combo box with a pull down of
"And"
"Or"

Private sub myCombo_afterUpdate()
andOr = me.myCombo
end sub

You would need to ensure that "andOr" is equal to "And" or "Or" before running the code.

 

[Elvis72]

How would I ensure that "andOr" is equal to "And or "Or" before running the code?

 

[Elvis72]

Because when I switch to form view I am getting:

Undefined function 'andOr' in expression.

 

[MajP]

Lots of different ways depending on how you would like your user interface. Some possibilities:
1)Set the default value of your control to one of the values
"And" or "Or"
2)Set the value of the "andOr" variable in the forms open event to a default.
3) If the user has not selected a choice pop up a message saying you have to pick either "And" or "Or". Something like

if andOr = "" then
msgbox "Use the combo box to pick what type of search"
exit sub
else
do your search code
end if

 

[Elvis72]

OK, I took the combobox and gave it the default value of AND...but I'm still getting the error...?

Option Compare Database
Public andOr As String


Private Sub Combo53_AfterUpdate()
andOr = Combo53
End Sub

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 & "*"")" & andOr
End If

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

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

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

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

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

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

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

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

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

If Not IsNull(Me.memoresume) Then
strWhere = strWhere & "([Resume Memo] Like ""*" & Me.memoresume & "*"")" & andOr
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]

Please provide error number and description.

Here is one problem (my fault)
Me.txtfirstname & "*"")" & andOr
add a space on all strings after the )
Me.txtfirstname & "*"") " & andOr

Currently you are getting something like

John*)And
not
John*) And

 

[Elvis72]

Good Morning!~

I changed the " above with spaces and I'm still getting the error:

Undefined Function 'andOr' in expression.

Here is what I have selected for the Combobox

Row Source Type: Value List
Row Source "AND"; "OR"
I have had the Default Value set to "AND" then nothing and I get the same error.



Here are the current events:

Option Compare Database
Public andOr As String


Private Sub Combo53_AfterUpdate()
andOr = Combo53
End Sub

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 & "*"") " & andOr
End If

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

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

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

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

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

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

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

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

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

If Not IsNull(Me.memoresume) Then
strWhere = strWhere & "([Resume Memo] Like ""*" & Me.memoresume & "*"") " & andOr
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

 

[Elvis72]

OK...oddly enough when I exit out of the form and open it on its own it opens...but when I select AND from the drop down and then put something in 2 fields to search it tells me:

Run-time error '2448':

You can't assign a value to this object

When I hit debug it highlights:

Me.Filter = strWhere

 

[MajP]

you have a debug.print so comment out the me.filter and post the results from the immediate window. It should be a long "where" string.

Also you can put this directly into your main procedure at the top.

...
Const conJetDate = "\#mm\/dd\/yyyy\#"
andOr = me.combo53.value
rest of your code

 

[Elvis72]

OK...so I commented out the me.filter = strwhere

Then it didn't like the Me.FilterOn = True

So I commented that out...

When I do that the form does nothing...

If take the comments off and just use the provided:

Const conJetDate = "\#mm\/dd\/yyyy\#"
andOr = me.combo53.value

It is giving me:

Run-time error '3075'

Syntax error in string in query expression

'([Experience] Like "*Proposal*") PR ([Plant Services] = False) OR ([Resume Memo] Like "*proposal*'.

 

[PHV]

Replace this:
lngLen = Len(strWhere) - 5
with this:
lngLen = Len(strWhere) - Len(Trim(andOr)) - 2

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Elvis72]

OK...I have the updated beginning:

Const conJetDate = "\#mm\/dd\/yyyy\#"
andOr = me.combo53.value

The commented:

me.Filter = strwhere

me.FilterOn = True

And I changed:

lngLen = Len(strWhere) - 5

To This:

lngLen = Len(strWhere) - Len(Trim(andOr)) - 2

Now I don't get any response from the filter when I run:

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

 

[PHV]

As you don't use extra space:
lngLen = Len(strWhere) - Len(Trim(andOr)) - [!]1[/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Elvis72]

Regardless if I change to a - 1 the form still does nothing?

 

[MajP]

I see one more thing. You will need the space after the andOr

" & andOr & " "

because when you add the two strings together
([Experience] Like "*Proposal*") OR([Resume Memo] Like "*proposal*"

you need a space after your "OR" or "AND"

so when you are all done your string will have at the end either
_AND_
or
_OR_

with a space following them
So correct
" & andOr & " "
throughout the code and the -2 should work

 

[Elvis72]

It now says:

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

In the Filter but it is still not Filtering the Form?