Append a wildcard to a text search string

  • Thread starter Thread starter tstew
  • Start date Start date
T

tstew

Hello,

I have an unbound text box on a form (single) and I want to search one field
in my database for a matching record. The field is house addresses like "1234
Main St. #12" or "5678 Rancho Santa Margarita". The code below is the event
procedure for a search button. It almost does what I want, but I have to type
a wildcard in the text string to get it to work. I can't seem to come up with
the right syntax to append the wildcard. Ideas?

I'm going to post a seperate question on the bb, but I'll post it here in
the hopes some knowledgeable person can address both issues. I also can't
seem to use the "FindNext" command. I'm trying to just replace "FindRecord"
with "FindNext" in the code below and I get a compile error "Too few
arguments". Ideas on this one?

BTW, I've tried going through Allen Browne's code and that is over my head.

THANKS!
Mark
Private Sub CMDFindAdd_Click()
Dim strAddressRef As String
Dim strFindAdd As String

'Check txtFindAdd for Null value or Nill Entry first.

If IsNull(Me![txtfindadd]) Or (Me![txtfindadd]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search!"
Me![txtfindadd].SetFocus
Exit Sub
End If
'---------------------------------------------------------------

'Performs the search using value entered into txtfindadd
'and evaluates this against values in Address

DoCmd.ShowAllRecords
DoCmd.GoToControl ("Address")
DoCmd.FindRecord Me!txtSearch

ADDRESS.SetFocus
strAddressRef = ADDRESS.Text
txtfindadd.SetFocus
strFindAdd = txtfindadd.Text

'If matching record found sets focus in PicLocation

If strAddressRef = txtfindadd Then
ADDRESS.SetFocus

'If value not found sets focus back to txtSearch and shows msgbox
Else
MsgBox "Match Not Found For: " & strSearch & " - Please Try
Again.", _
, "Invalid Search"
txtfindadd.SetFocus
End If
End Sub
 
Hi stew,
assuming you have a search button on the form, you can put this code on the
click event for the button

If Len(Me.txtFilterAddress & vbNullString) > 0 Then
strWhere = strWhere & "([Address] Like """ & Me.txtFilterAddress
& "*"") AND "
End If

Me.Filter = strWhere
Me.FilterOn = True

Note: the code assumes an unbound textbox called txtFilterAddress and
a field in the form's recordsource called Address.

Change txtFilterAddress and Address to suit your database.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


tstew said:
Hello,

I have an unbound text box on a form (single) and I want to search one
field
in my database for a matching record. The field is house addresses like
"1234
Main St. #12" or "5678 Rancho Santa Margarita". The code below is the
event
procedure for a search button. It almost does what I want, but I have to
type
a wildcard in the text string to get it to work. I can't seem to come up
with
the right syntax to append the wildcard. Ideas?

I'm going to post a seperate question on the bb, but I'll post it here in
the hopes some knowledgeable person can address both issues. I also can't
seem to use the "FindNext" command. I'm trying to just replace
"FindRecord"
with "FindNext" in the code below and I get a compile error "Too few
arguments". Ideas on this one?

BTW, I've tried going through Allen Browne's code and that is over my
head.

THANKS!
Mark
Private Sub CMDFindAdd_Click()
Dim strAddressRef As String
Dim strFindAdd As String

'Check txtFindAdd for Null value or Nill Entry first.

If IsNull(Me![txtfindadd]) Or (Me![txtfindadd]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search!"
Me![txtfindadd].SetFocus
Exit Sub
End If
'---------------------------------------------------------------

'Performs the search using value entered into txtfindadd
'and evaluates this against values in Address

DoCmd.ShowAllRecords
DoCmd.GoToControl ("Address")
DoCmd.FindRecord Me!txtSearch

ADDRESS.SetFocus
strAddressRef = ADDRESS.Text
txtfindadd.SetFocus
strFindAdd = txtfindadd.Text

'If matching record found sets focus in PicLocation

If strAddressRef = txtfindadd Then
ADDRESS.SetFocus

'If value not found sets focus back to txtSearch and shows msgbox
Else
MsgBox "Match Not Found For: " & strSearch & " - Please Try
Again.", _
, "Invalid Search"
txtfindadd.SetFocus
End If
End Sub
Click to expand...
 
Correction, left out dimming strWhere
Here is the complete code:

--------------------------
Dim strWhere As String

If Len(Me.txtFilterAddress & vbNullString) > 0 Then
strWhere = "([Address] Like """ & Me.txtFilterAddress & "*"") AND "
End If

Me.Filter = strWhere
Me.FilterOn = True
--------------------------
Note: the code assumes an unbound textbox called txtFilterAddress and
a field in the form's recordsource called Address.

Change txtFilterAddress and Address to suit your database.

Watch the line wrap, this bit should be all on one line.
strWhere = "([Address] Like """ & Me.txtFilterAddress & "*"") AND "


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Jeanette Cunningham said:
Hi stew,
assuming you have a search button on the form, you can put this code on
the click event for the button

If Len(Me.txtFilterAddress & vbNullString) > 0 Then
strWhere = strWhere & "([Address] Like """ &
Me.txtFilterAddress & "*"") AND "
End If

Me.Filter = strWhere
Me.FilterOn = True

Note: the code assumes an unbound textbox called txtFilterAddress and
a field in the form's recordsource called Address.

Change txtFilterAddress and Address to suit your database.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


tstew said:
Hello,

I have an unbound text box on a form (single) and I want to search one
field
in my database for a matching record. The field is house addresses like
"1234
Main St. #12" or "5678 Rancho Santa Margarita". The code below is the
event
procedure for a search button. It almost does what I want, but I have to
type
a wildcard in the text string to get it to work. I can't seem to come up
with
the right syntax to append the wildcard. Ideas?

I'm going to post a seperate question on the bb, but I'll post it here in
the hopes some knowledgeable person can address both issues. I also can't
seem to use the "FindNext" command. I'm trying to just replace
"FindRecord"
with "FindNext" in the code below and I get a compile error "Too few
arguments". Ideas on this one?

BTW, I've tried going through Allen Browne's code and that is over my
head.

THANKS!
Mark
Private Sub CMDFindAdd_Click()
Dim strAddressRef As String
Dim strFindAdd As String

'Check txtFindAdd for Null value or Nill Entry first.

If IsNull(Me![txtfindadd]) Or (Me![txtfindadd]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search!"
Me![txtfindadd].SetFocus
Exit Sub
End If
'---------------------------------------------------------------

'Performs the search using value entered into txtfindadd
'and evaluates this against values in Address

DoCmd.ShowAllRecords
DoCmd.GoToControl ("Address")
DoCmd.FindRecord Me!txtSearch

ADDRESS.SetFocus
strAddressRef = ADDRESS.Text
txtfindadd.SetFocus
strFindAdd = txtfindadd.Text

'If matching record found sets focus in PicLocation

If strAddressRef = txtfindadd Then
ADDRESS.SetFocus

'If value not found sets focus back to txtSearch and shows msgbox
Else
MsgBox "Match Not Found For: " & strSearch & " - Please Try
Again.", _
, "Invalid Search"
txtfindadd.SetFocus
End If
End Sub
Click to expand...
Click to expand...
 
Correction, forgot to remove an AND at the end of the filter line.
I just lifted this code from an app that filters several textboxes. I should
have started writing it from scratch instead!

--------------------------
Dim strWhere As String

If Len(Me.txtFilterAddress & vbNullString) > 0 Then
strWhere = "([Address] Like """ & Me.txtFilterAddress & "*"") "
End If

Me.Filter = strWhere
Me.FilterOn = True
--------------------------
Note: the code assumes an unbound textbox called txtFilterAddress and
a field in the form's recordsource called Address.

Change txtFilterAddress and Address to suit your database.

Watch the line wrap, this bit should be all on one line.
strWhere = "([Address] Like """ & Me.txtFilterAddress & "*"")"


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Jeanette Cunningham said:
Correction, left out dimming strWhere
Here is the complete code:

--------------------------
Dim strWhere As String

If Len(Me.txtFilterAddress & vbNullString) > 0 Then
strWhere = "([Address] Like """ & Me.txtFilterAddress & "*"") AND "
End If

Me.Filter = strWhere
Me.FilterOn = True
--------------------------
Note: the code assumes an unbound textbox called txtFilterAddress and
a field in the form's recordsource called Address.

Change txtFilterAddress and Address to suit your database.

Watch the line wrap, this bit should be all on one line.
strWhere = "([Address] Like """ & Me.txtFilterAddress & "*"") AND "


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Jeanette Cunningham said:
Hi stew,
assuming you have a search button on the form, you can put this code on
the click event for the button

If Len(Me.txtFilterAddress & vbNullString) > 0 Then
strWhere = strWhere & "([Address] Like """ &
Me.txtFilterAddress & "*"") AND "
End If

Me.Filter = strWhere
Me.FilterOn = True

Note: the code assumes an unbound textbox called txtFilterAddress and
a field in the form's recordsource called Address.

Change txtFilterAddress and Address to suit your database.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


tstew said:
Hello,

I have an unbound text box on a form (single) and I want to search one
field
in my database for a matching record. The field is house addresses like
"1234
Main St. #12" or "5678 Rancho Santa Margarita". The code below is the
event
procedure for a search button. It almost does what I want, but I have to
type
a wildcard in the text string to get it to work. I can't seem to come up
with
the right syntax to append the wildcard. Ideas?

I'm going to post a seperate question on the bb, but I'll post it here
in
the hopes some knowledgeable person can address both issues. I also
can't
seem to use the "FindNext" command. I'm trying to just replace
"FindRecord"
with "FindNext" in the code below and I get a compile error "Too few
arguments". Ideas on this one?

BTW, I've tried going through Allen Browne's code and that is over my
head.

THANKS!
Mark


Private Sub CMDFindAdd_Click()
Dim strAddressRef As String
Dim strFindAdd As String

'Check txtFindAdd for Null value or Nill Entry first.

If IsNull(Me![txtfindadd]) Or (Me![txtfindadd]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search!"
Me![txtfindadd].SetFocus
Exit Sub
End If
'---------------------------------------------------------------

'Performs the search using value entered into txtfindadd
'and evaluates this against values in Address

DoCmd.ShowAllRecords
DoCmd.GoToControl ("Address")
DoCmd.FindRecord Me!txtSearch

ADDRESS.SetFocus
strAddressRef = ADDRESS.Text
txtfindadd.SetFocus
strFindAdd = txtfindadd.Text

'If matching record found sets focus in PicLocation

If strAddressRef = txtfindadd Then
ADDRESS.SetFocus

'If value not found sets focus back to txtSearch and shows msgbox
Else
MsgBox "Match Not Found For: " & strSearch & " - Please Try
Again.", _
, "Invalid Search"
txtfindadd.SetFocus
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top