filter with wildcard

J

JB

I have a form that I want to enable users to filter by entering a search
criteria and then display only records that display that criteria in any of 3
columns.

example.

If user enters Pat in the txtSearch field, records that contain patricia
smith, john patterson, Spatterri & Co in the ContactName, Company_Name or
Name fields.

Here is the code I have used without success.
If somebody could tell me what I'm doing wrong it would be appreciated.

Private Sub btn_search_Click()
If Not IsNull(TxtSearch) Then

Dim stSearchCriteria As String

stSearchCriteria = "[ContactName]=" & "'*" & Me![TxtSearch] & "*'" Or
"[Company_Name]=" & "'*" & Me![TxtSearch] & "*'" Or "[Name]=" & "'*" &
Me![TxtSearch] & "*'"

DoCmd.ApplyFilter , stSearchCriteria

Else

MsgBox "Enter the text to search for", vbOKOnly, "Search Text"

End If

End Sub
 
Y

Yanick

Try this:

stSearchCriteria = "[ContactName] Like '*" & Me![TxtSearch] & "*'" Or
"[Company_Name] Like '*" & Me![TxtSearch] & "*'" Or "[Name] Like '*" &
Me![TxtSearch] & "*'"

Should work

Yanick
 
M

Marshall Barton

JB said:
I have a form that I want to enable users to filter by entering a search
criteria and then display only records that display that criteria in any of 3
columns.

example.

If user enters Pat in the txtSearch field, records that contain patricia
smith, john patterson, Spatterri & Co in the ContactName, Company_Name or
Name fields.

Here is the code I have used without success.
If somebody could tell me what I'm doing wrong it would be appreciated.

Private Sub btn_search_Click()
If Not IsNull(TxtSearch) Then

Dim stSearchCriteria As String

stSearchCriteria = "[ContactName]=" & "'*" & Me![TxtSearch] & "*'" Or
"[Company_Name]=" & "'*" & Me![TxtSearch] & "*'" Or "[Name]=" & "'*" &
Me![TxtSearch] & "*'"

DoCmd.ApplyFilter , stSearchCriteria
Click to expand...
[]

Whenever you use wildcards you must use the LIKE operator
instead of =
 
J

JB

Thanks Yanick,

When I do the search on 1 field only it works, but when I try to search on 2
or more fields I get a 'Run-time error '13' Type mismatch'.

I can settle for having 3 buttons dependant on which field the user wants to
search.

Yanick said:
Try this:

stSearchCriteria = "[ContactName] Like '*" & Me![TxtSearch] & "*'" Or
"[Company_Name] Like '*" & Me![TxtSearch] & "*'" Or "[Name] Like '*" &
Me![TxtSearch] & "*'"

Should work

Yanick

JB said:
I have a form that I want to enable users to filter by entering a search
criteria and then display only records that display that criteria in any of 3
columns.

example.

If user enters Pat in the txtSearch field, records that contain patricia
smith, john patterson, Spatterri & Co in the ContactName, Company_Name or
Name fields.

Here is the code I have used without success.
If somebody could tell me what I'm doing wrong it would be appreciated.

Private Sub btn_search_Click()
If Not IsNull(TxtSearch) Then

Dim stSearchCriteria As String

stSearchCriteria = "[ContactName]=" & "'*" & Me![TxtSearch] & "*'" Or
"[Company_Name]=" & "'*" & Me![TxtSearch] & "*'" Or "[Name]=" & "'*" &
Me![TxtSearch] & "*'"

DoCmd.ApplyFilter , stSearchCriteria

Else

MsgBox "Enter the text to search for", vbOKOnly, "Search Text"

End If

End Sub
Click to expand...
Click to expand...
 
Y

Yanick

Oups... try this ones :

"[ContactName] Like '*" & Me![TxtSearch] & "*' Or [Company_Name] Like '*" &
Me![TxtSearch] & "*' Or [Name] Like '*" & Me![TxtSearch] & "*'"

Yanick

JB said:
Thanks Yanick,

When I do the search on 1 field only it works, but when I try to search on 2
or more fields I get a 'Run-time error '13' Type mismatch'.

I can settle for having 3 buttons dependant on which field the user wants to
search.

Yanick said:
Try this:

stSearchCriteria = "[ContactName] Like '*" & Me![TxtSearch] & "*'" Or
"[Company_Name] Like '*" & Me![TxtSearch] & "*'" Or "[Name] Like '*" &
Me![TxtSearch] & "*'"

Should work

Yanick

JB said:
I have a form that I want to enable users to filter by entering a search
criteria and then display only records that display that criteria in any of 3
columns.

example.

If user enters Pat in the txtSearch field, records that contain patricia
smith, john patterson, Spatterri & Co in the ContactName, Company_Name or
Name fields.

Here is the code I have used without success.
If somebody could tell me what I'm doing wrong it would be appreciated.

Private Sub btn_search_Click()
If Not IsNull(TxtSearch) Then

Dim stSearchCriteria As String

stSearchCriteria = "[ContactName]=" & "'*" & Me![TxtSearch] & "*'" Or
"[Company_Name]=" & "'*" & Me![TxtSearch] & "*'" Or "[Name]=" & "'*" &
Me![TxtSearch] & "*'"

DoCmd.ApplyFilter , stSearchCriteria

Else

MsgBox "Enter the text to search for", vbOKOnly, "Search Text"

End If

End Sub
Click to expand...
Click to expand...
Click to expand...
 
J

JB

Thanks Yanick.

Works perfect.

Yanick said:
Oups... try this ones :

"[ContactName] Like '*" & Me![TxtSearch] & "*' Or [Company_Name] Like '*" &
Me![TxtSearch] & "*' Or [Name] Like '*" & Me![TxtSearch] & "*'"

Yanick

JB said:
Thanks Yanick,

When I do the search on 1 field only it works, but when I try to search on 2
or more fields I get a 'Run-time error '13' Type mismatch'.

I can settle for having 3 buttons dependant on which field the user wants to
search.

Yanick said:
Try this:

stSearchCriteria = "[ContactName] Like '*" & Me![TxtSearch] & "*'" Or
"[Company_Name] Like '*" & Me![TxtSearch] & "*'" Or "[Name] Like '*" &
Me![TxtSearch] & "*'"

Should work

Yanick

:

I have a form that I want to enable users to filter by entering a search
criteria and then display only records that display that criteria in any of 3
columns.

example.

If user enters Pat in the txtSearch field, records that contain patricia
smith, john patterson, Spatterri & Co in the ContactName, Company_Name or
Name fields.

Here is the code I have used without success.
If somebody could tell me what I'm doing wrong it would be appreciated.

Private Sub btn_search_Click()
If Not IsNull(TxtSearch) Then

Dim stSearchCriteria As String

stSearchCriteria = "[ContactName]=" & "'*" & Me![TxtSearch] & "*'" Or
"[Company_Name]=" & "'*" & Me![TxtSearch] & "*'" Or "[Name]=" & "'*" &
Me![TxtSearch] & "*'"

DoCmd.ApplyFilter , stSearchCriteria

Else

MsgBox "Enter the text to search for", vbOKOnly, "Search Text"

End If

End Sub
Click to expand...
Click to expand...
Click to expand...
 
E

eos

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George
 
E

eos

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George
 
E

eos

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George
 
E

eos

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George
 
E

eos

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George
 
E

eos

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Detail in form goes blank 5
Search form help 3
Optional Search 0
Search criteria 9
Need help with programming again 2
Enter new record if search returns no results? 17
Help with search code 4
Search multiple forms across multiple tables, fields and records 1

Top