Like Clause in MS-Access

[Mike Charney]

Can anyone tell me why the following statement doesnt work?

DoCmd.OpenForm "frmSearchResults", , , "text24 Like *" & filter1 & "*"

Field text24 is the field on teh results form that I am trying to pattern
match on.

Thanks.

Mike
m charney at dunlap hospital dot org

[Rich P]

You need to delimit your parameter. I am guessing that it is a text
param so you delimit it with single quotes '. Place a single quote
before the first '* and after the second *'

DoCmd.OpenForm "frmSearchResults", , , "text24 Like '*" & filter1 & "*'"
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Mike Charney]

I think I got the command to work, except it opens a blank form with nothing
on it. None of the controls or text I have put on the form show up.

The command is:

DoCmd.OpenForm "frmSearchResults", , , Text24 Like "*" & filter1 & "*"

Text24 is the control on the results form that holds the Asset Tag #
filter1 is the data that the user enters on the search form to earch for an
asset tag #

I have checked the data in the table and the record is present. The asset
tag # is DMH10257. I tried to str(filter1) but it made no difference.

Does any one have an idea?

Mike

"Rich P" <rp*****@aol.com> wrote in message
news:3u**************@news.uswest.net...

You need to delimit your parameter. I am guessing that it is a text
param so you delimit it with single quotes '. Place a single quote
before the first '* and after the second *'

DoCmd.OpenForm "frmSearchResults", , , "text24 Like '*" & filter1 & "*'"
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Randy Harris]

Mike Charney wrote:

I think I got the command to work, except it opens a blank form with nothing
on it. None of the controls or text I have put on the form show up.

The command is:

DoCmd.OpenForm "frmSearchResults", , , Text24 Like "*" & filter1 & "*"

Text24 is the control on the results form that holds the Asset Tag #
filter1 is the data that the user enters on the search form to earch for an
asset tag #

I have checked the data in the table and the record is present. The asset
tag # is DMH10257. I tried to str(filter1) but it made no difference.

Does any one have an idea?

Mike

"Rich P" <rp*****@aol.com> wrote in message
news:3u**************@news.uswest.net...

You need to delimit your parameter. I am guessing that it is a text
param so you delimit it with single quotes '. Place a single quote
before the first '* and after the second *'

DoCmd.OpenForm "frmSearchResults", , , "text24 Like '*" & filter1 & "*'"
Rich

*** Sent via Developersdex http://www.developersdex.com ***

Given the incomplete informmation provided, Rich's response was correct.

The WHERE clause needs to filter the data that is the RecordSource for
the form. Not the controls on the form. Is Text24 bound to a field in
the RecordSource? What is the name of the field that holds the Asset
Tag (hopefully, not Text24)?
--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

[chris.nebinger]

Say the user types in 2A3 for the Asset Tag. The proper format for
your command is:
DoCmd.OpenForm "frmSearchResults",,, "Text24 Like '*2A3*'"

That is: Text24 Like SingleQuote*2A3*SingleQuote - All surrounded by
double-quotes.

Because you want to replace the FIlter string with a variable, it
becomes:

DoCmd.OpenForm "frmSearchResults",,, "Text24 Like '*" & filter1 & "*'"

That is:

"Text24 Like SINGLEQUOTE*DoubleQuote & filter1 &
DoubleQuote*SingleQuoteDoubleQuote

Chris Nebinger

[Mike Charney]

Sorry for the incomplete information.

I did figure out the LIKE command but it seems that it returns no records
when I enter a number, like 257. The asset tag field holds DMH10257. I try
to search by 257 and I get no records using the LIKE command. The DMH part
of the field never changes, just the number.

The command I am using is:

DoCmd.OpenForm "frmSearchResults", , , assettag Like "*" & filter1 & "*"

I did have text24 instead of asset tag and I figured out that text24 was a
control that was not bound to a field. I fixed it but it will not return any
records. I also tried using the ' (single quote) mark but when I do I
receive a popup box asking me to type in data for the assettag field.

I guess I don't understand why the LIKE command doesn't work in the where
clause in an mdb file.

<ch************@gmail.com> wrote in message
news:11*********************@j33g2000cwa.googlegro ups.com...

Say the user types in 2A3 for the Asset Tag. The proper format for
your command is:
DoCmd.OpenForm "frmSearchResults",,, "Text24 Like '*2A3*'"

That is: Text24 Like SingleQuote*2A3*SingleQuote - All surrounded by
double-quotes.

Because you want to replace the FIlter string with a variable, it
becomes:

DoCmd.OpenForm "frmSearchResults",,, "Text24 Like '*" & filter1 & "*'"

That is:

"Text24 Like SINGLEQUOTE*DoubleQuote & filter1 &
DoubleQuote*SingleQuoteDoubleQuote

Chris Nebinger

[Randy Harris]

Mike Charney wrote:

Sorry for the incomplete information.

I did figure out the LIKE command but it seems that it returns no records
when I enter a number, like 257. The asset tag field holds DMH10257. I try
to search by 257 and I get no records using the LIKE command. The DMH part
of the field never changes, just the number.

The command I am using is:

DoCmd.OpenForm "frmSearchResults", , , assettag Like "*" & filter1 & "*"

I did have text24 instead of asset tag and I figured out that text24 was a
control that was not bound to a field. I fixed it but it will not return any
records. I also tried using the ' (single quote) mark but when I do I
receive a popup box asking me to type in data for the assettag field.

I guess I don't understand why the LIKE command doesn't work in the where
clause in an mdb file.

It's not going to work if you don't enclose the WHERE clause in quotes.
If assettag is a field in the recordsource for the form and filter1
contains your target value, such as 257, this syntax will work.

DoCmd.OpenForm "frmSearchResults", , , "assettag Like ""*" & filter1 & "*"""

(that was all on one line)

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

[Mike Charney]

Thanks for everyones help.

I have it working.

Mike

"Randy Harris" <pl****@send.no.spam> wrote in message
news:rU*******************@newssvr21.news.prodigy. com...

Mike Charney wrote:

Sorry for the incomplete information.

I did figure out the LIKE command but it seems that it returns no records
when I enter a number, like 257. The asset tag field holds DMH10257. I
try to search by 257 and I get no records using the LIKE command. The DMH
part of the field never changes, just the number.

The command I am using is:

DoCmd.OpenForm "frmSearchResults", , , assettag Like "*" & filter1 & "*"

I did have text24 instead of asset tag and I figured out that text24 was
a control that was not bound to a field. I fixed it but it will not
return any records. I also tried using the ' (single quote) mark but when
I do I receive a popup box asking me to type in data for the assettag
field.

I guess I don't understand why the LIKE command doesn't work in the where
clause in an mdb file.

It's not going to work if you don't enclose the WHERE clause in quotes. If
assettag is a field in the recordsource for the form and filter1 contains
your target value, such as 257, this syntax will work.

DoCmd.OpenForm "frmSearchResults", , , "assettag Like ""*" & filter1 &
"*"""

(that was all on one line)

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.