Like statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I get records returned with the following:

Like[BAU214, BAU215, BAU216]

I would like to have all three records returned in the query. I can't seem
to figure it out.

How would that query look?

Thanks.
 
If it is only those 3, use the IN predicate:
IN("BAU214", "BAU215", "BAU216")

If you want to return everything that stars with BAU, it would be:
LIKE "BAU*"
 
I think you would rather use IN than LIKE.

tbl_rml:
rml_number - text - PK - BAU214, BAU217, BAU215, BAU216, etc
some other fields just for testing purposes

Then I ran this query:
SELECT tbl_rml.*
FROM tbl_rml
WHERE rml_number IN ("BAU214","BAU215","BAU216")

Give it a try

Cheers,
Jason Lepack
 
The information is actually in field on a form. When you hit the command
button, it runs the query with that information. It could have one to 10
entries.
Does that change things?

Thanks.

Klatuu said:
If it is only those 3, use the IN predicate:
IN("BAU214", "BAU215", "BAU216")

If you want to return everything that stars with BAU, it would be:
LIKE "BAU*"
--
Dave Hargis, Microsoft Access MVP


rml said:
How do I get records returned with the following:

Like[BAU214, BAU215, BAU216]

I would like to have all three records returned in the query. I can't seem
to figure it out.

How would that query look?

Thanks.
Click to expand...
Click to expand...
 
Are you saying the user would enter data in a control on the form like this:
BAU214, BAU215, BAU216

And that is what you want to search on?
--
Dave Hargis, Microsoft Access MVP


rml said:
The information is actually in field on a form. When you hit the command
button, it runs the query with that information. It could have one to 10
entries.
Does that change things?

Thanks.

Klatuu said:
If it is only those 3, use the IN predicate:
IN("BAU214", "BAU215", "BAU216")

If you want to return everything that stars with BAU, it would be:
LIKE "BAU*"
--
Dave Hargis, Microsoft Access MVP


rml said:
How do I get records returned with the following:

Like[BAU214, BAU215, BAU216]

I would like to have all three records returned in the query. I can't seem
to figure it out.

How would that query look?

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Yes. It should return the those records.

Thanks.

Klatuu said:
Are you saying the user would enter data in a control on the form like this:
BAU214, BAU215, BAU216

And that is what you want to search on?
--
Dave Hargis, Microsoft Access MVP


rml said:
The information is actually in field on a form. When you hit the command
button, it runs the query with that information. It could have one to 10
entries.
Does that change things?

Thanks.

Klatuu said:
If it is only those 3, use the IN predicate:
IN("BAU214", "BAU215", "BAU216")

If you want to return everything that stars with BAU, it would be:
LIKE "BAU*"
--
Dave Hargis, Microsoft Access MVP


:

How do I get records returned with the following:

Like[BAU214, BAU215, BAU216]

I would like to have all three records returned in the query. I can't seem
to figure it out.

How would that query look?

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Then you will need to write some VBA to parse the control and create the
Where clause of the query.
--
Dave Hargis, Microsoft Access MVP


rml said:
Yes. It should return the those records.

Thanks.

Klatuu said:
Are you saying the user would enter data in a control on the form like this:
BAU214, BAU215, BAU216

And that is what you want to search on?
--
Dave Hargis, Microsoft Access MVP


rml said:
The information is actually in field on a form. When you hit the command
button, it runs the query with that information. It could have one to 10
entries.
Does that change things?

Thanks.

:

If it is only those 3, use the IN predicate:
IN("BAU214", "BAU215", "BAU216")

If you want to return everything that stars with BAU, it would be:
LIKE "BAU*"
--
Dave Hargis, Microsoft Access MVP


:

How do I get records returned with the following:

Like[BAU214, BAU215, BAU216]

I would like to have all three records returned in the query. I can't seem
to figure it out.

How would that query look?

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
rml said:
How do I get records returned with the following:

Like[BAU214, BAU215, BAU216]

I would like to have all three records returned in the query.
Click to expand...


Like "BAU21[456]"

or if the values are ot that uniform:

IN("BAU214", "BAU215"," BAU216")
 
Hi,

try this
----
SELECT *
FROM Table1
WHERE Forms!Form1!Text1 LIKE '*' & FieldName & '*'
----

bye
--
Giorgio Rancati
[Office Access MVP]

rml said:
The information is actually in field on a form. When you hit the command
button, it runs the query with that information. It could have one to 10
entries.
Does that change things?

Thanks.

Klatuu said:
If it is only those 3, use the IN predicate:
IN("BAU214", "BAU215", "BAU216")

If you want to return everything that stars with BAU, it would be:
LIKE "BAU*"
--
Dave Hargis, Microsoft Access MVP


rml said:
How do I get records returned with the following:

Like[BAU214, BAU215, BAU216]

I would like to have all three records returned in the query. I can't seem
to figure it out.

How would that query look?

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
No, it could be CAU34, BAU44, AVC67

I just want to be able to have the data in this field populate a query that
would return the records (if any) for those entered in that field.


Marshall Barton said:
rml said:
How do I get records returned with the following:

Like[BAU214, BAU215, BAU216]

I would like to have all three records returned in the query.
Click to expand...


Like "BAU21[456]"

or if the values are ot that uniform:

IN("BAU214", "BAU215"," BAU216")
Click to expand...
 
Then use Giorgio's idea.

To prevent matching just part of the specified values, use a
variation something like:

SELECT *
FROM Table1
WHERE "," & Forms!Form1!Text1 & "," LIKE "*," & FieldName &
".*"

And make sure there are no spaces in the list in the text
box.
--
Marsh
MVP [MS Access]

No, it could be CAU34, BAU44, AVC67

I just want to be able to have the data in this field populate a query that
would return the records (if any) for those entered in that field.


Marshall Barton said:
rml said:
How do I get records returned with the following:

Like[BAU214, BAU215, BAU216]

I would like to have all three records returned in the query.
Click to expand...


Like "BAU21[456]"

or if the values are ot that uniform:

IN("BAU214", "BAU215"," BAU216")
Click to expand...
Click to expand...
 
No spaces? It will always look like this:

BAU214, BAU324, BAU444

It will have the value, comma, space.

Does that change anything?

Marshall Barton said:
Then use Giorgio's idea.

To prevent matching just part of the specified values, use a
variation something like:

SELECT *
FROM Table1
WHERE "," & Forms!Form1!Text1 & "," LIKE "*," & FieldName &
".*"

And make sure there are no spaces in the list in the text
box.
--
Marsh
MVP [MS Access]

No, it could be CAU34, BAU44, AVC67

I just want to be able to have the data in this field populate a query that
would return the records (if any) for those entered in that field.


Marshall Barton said:
rml wrote:

How do I get records returned with the following:

Like[BAU214, BAU215, BAU216]

I would like to have all three records returned in the query.


Like "BAU21[456]"

or if the values are ot that uniform:

IN("BAU214", "BAU215"," BAU216")
Click to expand...
Click to expand...
Click to expand...
 
A
That only changes it a little. Add a space after the
commas:

WHERE ", " & Forms!Form1!Text1 & ", " LIKE "*, " &
FieldName & ". *"

The important point here is that the list in the text box
must have the exact punctuation alowed for in the comparison
expression.

Because the check for punctuation is used instead of just
the field, the criteria will not be able to take advantage
of any indexing, which may have signifiicant performance
impact.

Be sure you understand that using the punctuation in the
expression is there to prevent a field with a value like
AU21 from matching a list like XAU2156
--
Marsh
MVP [MS Access]

No spaces? It will always look like this:

BAU214, BAU324, BAU444

It will have the value, comma, space.

Does that change anything?

Marshall Barton said:
Then use Giorgio's idea.

To prevent matching just part of the specified values, use a
variation something like:

SELECT *
FROM Table1
WHERE "," & Forms!Form1!Text1 & "," LIKE "*," & FieldName &
".*"

And make sure there are no spaces in the list in the text
box.

No, it could be CAU34, BAU44, AVC67

I just want to be able to have the data in this field populate a query that
would return the records (if any) for those entered in that field.


:

rml wrote:

How do I get records returned with the following:

Like[BAU214, BAU215, BAU216]

I would like to have all three records returned in the query.


Like "BAU21[456]"

or if the values are ot that uniform:

IN("BAU214", "BAU215"," BAU216")
Click to expand...
Click to expand...
Click to expand...
 
I'm not getting it to work? Not sure what I might be doing wrong. Would it
be possible to send you the database (very small) so you can see what I'm
tring to do?

Thanks for your help.

Marshall Barton said:
A
That only changes it a little. Add a space after the
commas:

WHERE ", " & Forms!Form1!Text1 & ", " LIKE "*, " &
FieldName & ". *"

The important point here is that the list in the text box
must have the exact punctuation alowed for in the comparison
expression.

Because the check for punctuation is used instead of just
the field, the criteria will not be able to take advantage
of any indexing, which may have signifiicant performance
impact.

Be sure you understand that using the punctuation in the
expression is there to prevent a field with a value like
AU21 from matching a list like XAU2156
--
Marsh
MVP [MS Access]

No spaces? It will always look like this:

BAU214, BAU324, BAU444

It will have the value, comma, space.

Does that change anything?

Marshall Barton said:
Then use Giorgio's idea.

To prevent matching just part of the specified values, use a
variation something like:

SELECT *
FROM Table1
WHERE "," & Forms!Form1!Text1 & "," LIKE "*," & FieldName &
".*"

And make sure there are no spaces in the list in the text
box.


rml wrote:
No, it could be CAU34, BAU44, AVC67

I just want to be able to have the data in this field populate a query that
would return the records (if any) for those entered in that field.


:

rml wrote:

How do I get records returned with the following:

Like[BAU214, BAU215, BAU216]

I would like to have all three records returned in the query.


Like "BAU21[456]"

or if the values are ot that uniform:

IN("BAU214", "BAU215"," BAU216")
Click to expand...
Click to expand...
Click to expand...
 
Form1 is my form, Text1 would be the field on my form. What would be my
Fieldname?

Thanks.

giorgio rancati said:
Hi,

try this
----
SELECT *
FROM Table1
WHERE Forms!Form1!Text1 LIKE '*' & FieldName & '*'
----

bye
--
Giorgio Rancati
[Office Access MVP]

rml said:
The information is actually in field on a form. When you hit the command
button, it runs the query with that information. It could have one to 10
entries.
Does that change things?

Thanks.

Klatuu said:
If it is only those 3, use the IN predicate:
IN("BAU214", "BAU215", "BAU216")

If you want to return everything that stars with BAU, it would be:
LIKE "BAU*"
--
Dave Hargis, Microsoft Access MVP


:

How do I get records returned with the following:

Like[BAU214, BAU215, BAU216]

I would like to have all three records returned in the query. I can't seem
to figure it out.

How would that query look?

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
No, don't do that. I really don't want to go through the
process of verifying that it would be safe to open.

Just post the a Copy/Paste of the Where clause you have now
along with the test list you are using. Any explanation you
can provide about what it is doing would also help.
--
Marsh
MVP [MS Access]

I'm not getting it to work? Not sure what I might be doing wrong. Would it
be possible to send you the database (very small) so you can see what I'm
tring to do?

Marshall Barton said:
That only changes it a little. Add a space after the
commas:

WHERE ", " & Forms!Form1!Text1 & ", " LIKE "*, " &
FieldName & ". *"

The important point here is that the list in the text box
must have the exact punctuation alowed for in the comparison
expression.

Because the check for punctuation is used instead of just
the field, the criteria will not be able to take advantage
of any indexing, which may have signifiicant performance
impact.

Be sure you understand that using the punctuation in the
expression is there to prevent a field with a value like
AU21 from matching a list like XAU2156

No spaces? It will always look like this:

BAU214, BAU324, BAU444

It will have the value, comma, space.

Does that change anything?

:

Then use Giorgio's idea.

To prevent matching just part of the specified values, use a
variation something like:

SELECT *
FROM Table1
WHERE "," & Forms!Form1!Text1 & "," LIKE "*," & FieldName &
".*"

And make sure there are no spaces in the list in the text
box.


rml wrote:
No, it could be CAU34, BAU44, AVC67

I just want to be able to have the data in this field populate a query that
would return the records (if any) for those entered in that field.


:

rml wrote:

How do I get records returned with the following:

Like[BAU214, BAU215, BAU216]

I would like to have all three records returned in the query.


Like "BAU21[456]"

or if the values are ot that uniform:

IN("BAU214", "BAU215"," BAU216")
Click to expand...
Click to expand...
Click to expand...
 

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

WildCard in IIF Statement 2
Embedded 'Or' Criteria in an IIF Statement 7
Using IIF In Query Criteria 4
Query structure and distinct 7
Query for not 0 4
How do I query multiple data values in same field? 1
iif function syntax problem 5
parameter with iif statement in query criteria 8

Back
Top