building a string

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

Hello all. I've been unable to build a string to suit my purpose. Here are
the details. I have created a combo box, in the header of a form that is
unbound. The purpose is to be able to search for an address when you don't
know all of it, maybe just the street... the query behind the combo looks up
the street and the recordid and holds the recordid for later use. The combo
box should allow the user to type in the partial street information and find
the records that match so the customer can then select the appropriate record.
example of address: 123 anywhere street or 123 anywhere way, 123 mystreet
etc.. The customer migth only know "anywhere" as the street so they would
enter that in the combo box and the combo box would then limit the records
for selection to any address containing "anywhere" Here's the code I've been
working with. I also know this combo box code is placed in the after update
event. I just can't get the portion of the code to accept the "like "*" "
portion. In a query I would say: "like "*" & [Enter as much of the street as
you know] & "*" [addressm] is the field we're searching. Help is
appreciated. Thanks

Set crit1 = "*"
Set crit2 = "like"
criteria = "[addressm] = '" & crit2 & Me.cboquicksearch & crit2 & "'"
Set myrs = Me.RecordsetClone


myrs.FindFirst criteria
If Not myrs.NoMatch = True Then
Me.Bookmark = myrs.Bookmark
end if
 
Take a closer look at your code. Given that crit1 has been set to * and
crit2 to like, if cboquicksearch contains xyz, you're going to be generating
something like

[addressm] = 'likexyzlike'

when what you want is

[addressm] Like '*xyz*'


All you need is:

criteria = "[addressm] Like '*" & Me.cboquicksearch & "*'"


There's no value to having crit1 and crit2. BTW, if you do decide to keep
them, and go with

criteria = "[addressm] " & crit2 & '" & crit1 & Me.cboquicksearch & crit1 &
"'"

you can eliminate the Set keywords: they're not required with variables,
just with objects.
 
Thank you Douglas for your quick response....I'm still not getting this to
work.
This is what I'm doing and 'no records are found meeting my criteria" is the
response...
Private Sub cboquicksearch_AfterUpdate()
Me.Filter = "addressm = '" & "*" & Me.cboquicksearch & "*" & "'"
Me.FilterOn = True
End Sub
again, this is in the form header...
Any idea why this is not working? Addressm is on the form ans it's a field
in the combobox selection list.

Douglas J. Steele said:
Take a closer look at your code. Given that crit1 has been set to * and
crit2 to like, if cboquicksearch contains xyz, you're going to be generating
something like

[addressm] = 'likexyzlike'

when what you want is

[addressm] Like '*xyz*'


All you need is:

criteria = "[addressm] Like '*" & Me.cboquicksearch & "*'"


There's no value to having crit1 and crit2. BTW, if you do decide to keep
them, and go with

criteria = "[addressm] " & crit2 & '" & crit1 & Me.cboquicksearch & crit1 &
"'"

you can eliminate the Set keywords: they're not required with variables,
just with objects.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Dave said:
Hello all. I've been unable to build a string to suit my purpose. Here
are
the details. I have created a combo box, in the header of a form that is
unbound. The purpose is to be able to search for an address when you
don't
know all of it, maybe just the street... the query behind the combo looks
up
the street and the recordid and holds the recordid for later use. The
combo
box should allow the user to type in the partial street information and
find
the records that match so the customer can then select the appropriate
record.
example of address: 123 anywhere street or 123 anywhere way, 123 mystreet
etc.. The customer migth only know "anywhere" as the street so they would
enter that in the combo box and the combo box would then limit the records
for selection to any address containing "anywhere" Here's the code I've
been
working with. I also know this combo box code is placed in the after
update
event. I just can't get the portion of the code to accept the "like "*" "
portion. In a query I would say: "like "*" & [Enter as much of the street
as
you know] & "*" [addressm] is the field we're searching. Help is
appreciated. Thanks

Set crit1 = "*"
Set crit2 = "like"
criteria = "[addressm] = '" & crit2 & Me.cboquicksearch & crit2 & "'"
Set myrs = Me.RecordsetClone


myrs.FindFirst criteria
If Not myrs.NoMatch = True Then
Me.Bookmark = myrs.Bookmark
end if
Click to expand...
Click to expand...
 
Take a look at what I wrote. You need to use Like, not =.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Dave said:
Thank you Douglas for your quick response....I'm still not getting this to
work.
This is what I'm doing and 'no records are found meeting my criteria" is
the
response...
Private Sub cboquicksearch_AfterUpdate()
Me.Filter = "addressm = '" & "*" & Me.cboquicksearch & "*" & "'"
Me.FilterOn = True
End Sub
again, this is in the form header...
Any idea why this is not working? Addressm is on the form ans it's a
field
in the combobox selection list.

Douglas J. Steele said:
Take a closer look at your code. Given that crit1 has been set to * and
crit2 to like, if cboquicksearch contains xyz, you're going to be
generating
something like

[addressm] = 'likexyzlike'

when what you want is

[addressm] Like '*xyz*'


All you need is:

criteria = "[addressm] Like '*" & Me.cboquicksearch & "*'"


There's no value to having crit1 and crit2. BTW, if you do decide to keep
them, and go with

criteria = "[addressm] " & crit2 & '" & crit1 & Me.cboquicksearch & crit1
&
"'"

you can eliminate the Set keywords: they're not required with variables,
just with objects.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Dave said:
Hello all. I've been unable to build a string to suit my purpose.
Here
are
the details. I have created a combo box, in the header of a form that
is
unbound. The purpose is to be able to search for an address when you
don't
know all of it, maybe just the street... the query behind the combo
looks
up
the street and the recordid and holds the recordid for later use. The
combo
box should allow the user to type in the partial street information and
find
the records that match so the customer can then select the appropriate
record.
example of address: 123 anywhere street or 123 anywhere way, 123
mystreet
etc.. The customer migth only know "anywhere" as the street so they
would
enter that in the combo box and the combo box would then limit the
records
for selection to any address containing "anywhere" Here's the code
I've
been
working with. I also know this combo box code is placed in the after
update
event. I just can't get the portion of the code to accept the "like
"*" "
portion. In a query I would say: "like "*" & [Enter as much of the
street
as
you know] & "*" [addressm] is the field we're searching. Help is
appreciated. Thanks

Set crit1 = "*"
Set crit2 = "like"
criteria = "[addressm] = '" & crit2 & Me.cboquicksearch & crit2 & "'"
Set myrs = Me.RecordsetClone


myrs.FindFirst criteria
If Not myrs.NoMatch = True Then
Me.Bookmark = myrs.Bookmark
end if
Click to expand...
Click to expand...
Click to expand...
 
Back
Top