Filter for words at the beginning or end using a Form Command Butt

  • Thread starter Thread starter GUhas
  • Start date Start date
G

GUhas

I am filtering in a form for either customer name or phone number. I have
the following expression for the Filter Command Button. I can find, for
example, 'pet' in the customer-name field and 'pet' can appear anywhere. I
can't figure out how to change the code so I only find 'pet' when it is the
1st word in the Customer-name or the last word; or I only get '603' when it
is the area code (start of the phone number) in Telephone. I am using Access
2007. I borrowed this code from http://allenbrowne.com/ser-62.html.

Private Sub cmdFilter_Click()

Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.txtAcctName) Then
strWhere = strWhere & "([customer-name] Like ""*" & Me.txtAcctName &
"*"") AND "
End If

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


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

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
 
GUhas said:
I am filtering in a form for either customer name or phone number. I have
the following expression for the Filter Command Button. I can find, for
example, 'pet' in the customer-name field and 'pet' can appear anywhere.
I
can't figure out how to change the code so I only find 'pet' when it is
the
1st word in the Customer-name or the last word; or I only get '603' when
it
is the area code (start of the phone number) in Telephone. I am using
Access
2007. I borrowed this code from http://allenbrowne.com/ser-62.html.

Private Sub cmdFilter_Click()

Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.txtAcctName) Then
strWhere = strWhere & "([customer-name] Like ""*" & Me.txtAcctName
&
"*"") AND "
End If

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


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

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
Click to expand...


Determining whether "pet" is the first or last *word* is not simple, but
determining whether the name starts or ends with those characters is easy
enough. Try these replacement lines for building strWhere:

If Not IsNull(Me.txtAcctName) Then
strWhere = strWhere & _
"(([customer-name] Like """ & Me.txtAcctName & "*"") " & _
"OR " & _
"([customer-name] Like ""*" & Me.txtAcctName & """)) AND "
End If

If Not IsNull(Me.txtPhone) Then
strWhere = strWhere & _
"([telephone] Like """ & Me.txtPhone & "*"") AND "
End If

Leave the remainder of the code as it was.
 
First word should be easy: just look for a space after the word:

"(([customer-name] Like """ & Me.txtAcctName & " *"") "

Last word is a bit more difficult because of punctuation, although

"([customer-name] Like ""* " & Me.txtAcctName & "[.!]"")) "

may do it.

Hmm. You could, I suppose, have punctuation after the first word:

"(([customer-name] Like """ & Me.txtAcctName & "[ ,:;]*"") "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dirk Goldgar said:
GUhas said:
I am filtering in a form for either customer name or phone number. I have
the following expression for the Filter Command Button. I can find, for
example, 'pet' in the customer-name field and 'pet' can appear anywhere.
I
can't figure out how to change the code so I only find 'pet' when it is
the
1st word in the Customer-name or the last word; or I only get '603' when
it
is the area code (start of the phone number) in Telephone. I am using
Access
2007. I borrowed this code from http://allenbrowne.com/ser-62.html.

Private Sub cmdFilter_Click()

Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.txtAcctName) Then
strWhere = strWhere & "([customer-name] Like ""*" & Me.txtAcctName
&
"*"") AND "
End If

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


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

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
Click to expand...


Determining whether "pet" is the first or last *word* is not simple, but
determining whether the name starts or ends with those characters is easy
enough. Try these replacement lines for building strWhere:

If Not IsNull(Me.txtAcctName) Then
strWhere = strWhere & _
"(([customer-name] Like """ & Me.txtAcctName & "*"") " & _
"OR " & _
"([customer-name] Like ""*" & Me.txtAcctName & """)) AND "
End If

If Not IsNull(Me.txtPhone) Then
strWhere = strWhere & _
"([telephone] Like """ & Me.txtPhone & "*"") AND "
End If

Leave the remainder of the code as it was.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Dirk, that was PERFECT! Thank you so much!


Dirk Goldgar said:
GUhas said:
I am filtering in a form for either customer name or phone number. I have
the following expression for the Filter Command Button. I can find, for
example, 'pet' in the customer-name field and 'pet' can appear anywhere.
I
can't figure out how to change the code so I only find 'pet' when it is
the
1st word in the Customer-name or the last word; or I only get '603' when
it
is the area code (start of the phone number) in Telephone. I am using
Access
2007. I borrowed this code from http://allenbrowne.com/ser-62.html.

Private Sub cmdFilter_Click()

Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.txtAcctName) Then
strWhere = strWhere & "([customer-name] Like ""*" & Me.txtAcctName
&
"*"") AND "
End If

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


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

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
Click to expand...


Determining whether "pet" is the first or last *word* is not simple, but
determining whether the name starts or ends with those characters is easy
enough. Try these replacement lines for building strWhere:

If Not IsNull(Me.txtAcctName) Then
strWhere = strWhere & _
"(([customer-name] Like """ & Me.txtAcctName & "*"") " & _
"OR " & _
"([customer-name] Like ""*" & Me.txtAcctName & """)) AND "
End If

If Not IsNull(Me.txtPhone) Then
strWhere = strWhere & _
"([telephone] Like """ & Me.txtPhone & "*"") AND "
End If

Leave the remainder of the code as it was.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Douglas, Thank you so much!



Douglas J. Steele said:
First word should be easy: just look for a space after the word:

"(([customer-name] Like """ & Me.txtAcctName & " *"") "

Last word is a bit more difficult because of punctuation, although

"([customer-name] Like ""* " & Me.txtAcctName & "[.!]"")) "

may do it.

Hmm. You could, I suppose, have punctuation after the first word:

"(([customer-name] Like """ & Me.txtAcctName & "[ ,:;]*"") "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dirk Goldgar said:
GUhas said:
I am filtering in a form for either customer name or phone number. I have
the following expression for the Filter Command Button. I can find, for
example, 'pet' in the customer-name field and 'pet' can appear anywhere.
I
can't figure out how to change the code so I only find 'pet' when it is
the
1st word in the Customer-name or the last word; or I only get '603' when
it
is the area code (start of the phone number) in Telephone. I am using
Access
2007. I borrowed this code from http://allenbrowne.com/ser-62.html.

Private Sub cmdFilter_Click()

Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.txtAcctName) Then
strWhere = strWhere & "([customer-name] Like ""*" & Me.txtAcctName
&
"*"") AND "
End If

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


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

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
Click to expand...


Determining whether "pet" is the first or last *word* is not simple, but
determining whether the name starts or ends with those characters is easy
enough. Try these replacement lines for building strWhere:

If Not IsNull(Me.txtAcctName) Then
strWhere = strWhere & _
"(([customer-name] Like """ & Me.txtAcctName & "*"") " & _
"OR " & _
"([customer-name] Like ""*" & Me.txtAcctName & """)) AND "
End If

If Not IsNull(Me.txtPhone) Then
strWhere = strWhere & _
"([telephone] Like """ & Me.txtPhone & "*"") AND "
End If

Leave the remainder of the code as it was.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Click to expand...
 
Douglas J. Steele said:
First word should be easy: just look for a space after the word:

"(([customer-name] Like """ & Me.txtAcctName & " *"") "

Last word is a bit more difficult because of punctuation, although

"([customer-name] Like ""* " & Me.txtAcctName & "[.!]"")) "

may do it.

Hmm. You could, I suppose, have punctuation after the first word:

"(([customer-name] Like """ & Me.txtAcctName & "[ ,:;]*"") "
Click to expand...


Good suggestions. The complexity lies in determining exactly what
constitutes a word. If your selection of punctuation is deemed sufficient,
you still need to allow for punctuation *before* the word. Also, I don't
think your last-word expression will find the text entered if it is the last
word and there is *no* punctuation following, so -- since the Like operator
doesn't have an "end-of-line" specifier -- you need to test for that
separately. It gets complicated fast.
 
Back
Top