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

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

Post by R1VoYX » Thu, 13 Aug 2009 00:29:03


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://www.yqcomputer.com/

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
 
 
 

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

Post by Dirk Goldg » Thu, 13 Aug 2009 00:39:54


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)

 
 
 

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

Post by Douglas J. » Thu, 13 Aug 2009 00:56:29

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
http://www.yqcomputer.com/
(no e-mails, please!)
 
 
 

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

Post by Dirk Goldg » Thu, 13 Aug 2009 01:24:58


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.

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

(please reply to the newsgroup)