Question using Like in a query

[Guest]

It's been awhile since I've used SQL and I know there is a way to do this...

I'm trying to create a Select query that selects items, using "Like *.*'
from a table using a field from another table.

In Design View, Access is formatting the criteria as
"*

.[columnname]*" and this, of course, doesn't work very well.

There is a way to do a wildcard for this situation I just can't remember and
wading through all the available resources is proving fruitless.

I sure could use some help with this

 

[JohnFol]

you need to add "the other table" into the query ro use a DLookup to
explicitly return a single value back that can be used in the Like statement

 

[Guest]

That may work but, it's not how I remember being able to do the query. There
is some way to put asterisks around a tablename!columname in a query, on
not...

Also, how do you do a Like statement in a DLookup?

More info...

What I'm trying to do is execute a query that lists records from one table
(table A) using information in another table (table B). The information in
table A contains more descriptive text than in table B, so I need to be able
to do a Like *.* in order to get an accurate list from table A.

example: Table A contains an item, "Imbued Blackened Iron Maul" and Table B
contains, "Blackened Iron Maul"

I need to be able to pull the record from A since it matches using the Like
*.* criteria.

Thanks for you help

you need to add "the other table" into the query ro use a DLookup to
explicitly return a single value back that can be used in the Like statement

It's been awhile since I've used SQL and I know there is a way to do
this...

I'm trying to create a Select query that selects items, using "Like *.*'
from a table using a field from another table.

In Design View, Access is formatting the criteria as
"*

.[columnname]*" and this, of course, doesn't work very well.

There is a way to do a wildcard for this situation I just can't remember
and
wading through all the available resources is proving fruitless.

I sure could use some help with this

 

[JohnFol]

so bring both into the query as suggested and use

Select TableA.*, TableB.* from TableA, TableB where TableA.FieldA like "*" &
TableB.FieldA & "*"

this does mean you may get more results that expected. Unsing your example
you get a single match, but if there was a record containing "Another
Blackened Iron Maul" there would be 2 results. (2 x 1). The more entries the
quicker this multiplies out. ie 2 records in each table would give 4 rows
etc. . . .

That may work but, it's not how I remember being able to do the query.
There
is some way to put asterisks around a tablename!columname in a query, on
not...

Also, how do you do a Like statement in a DLookup?

More info...

What I'm trying to do is execute a query that lists records from one table
(table A) using information in another table (table B). The information
in
table A contains more descriptive text than in table B, so I need to be
able
to do a Like *.* in order to get an accurate list from table A.

example: Table A contains an item, "Imbued Blackened Iron Maul" and Table
B
contains, "Blackened Iron Maul"

I need to be able to pull the record from A since it matches using the
Like
*.* criteria.

Thanks for you help

you need to add "the other table" into the query ro use a DLookup to
explicitly return a single value back that can be used in the Like
statement

It's been awhile since I've used SQL and I know there is a way to do
this...

I'm trying to create a Select query that selects items, using "Like
*.*'
from a table using a field from another table.

In Design View, Access is formatting the criteria as
"*

.[columnname]*" and this, of course, doesn't work very well.

There is a way to do a wildcard for this situation I just can't
remember
and
wading through all the available resources is proving fruitless.

I sure could use some help with this

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Probably something like this:

SELECT A.item
FROM TableA As A, TableB As B
WHERE A.item Like "*" & B.item & "*"

If we used the "Blackened Iron Maul" as the value of B.item then, using
substitution (remember your high school algebra), the query's WHERE
clause would look like this:

WHERE A.item Like "*" & "Blackened Iron Maul" & "*"

Which should return the full item name, A.item, in the SELECT clause.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmUcjIechKqOuFEgEQJxsACgqAknR/gDaIOBTUa6PUAQvIIw/rAAoLqu
fHoWa475/uOK7JO4CLLSC4q2
=ag6Y
-----END PGP SIGNATURE-----

That may work but, it's not how I remember being able to do the query. There
is some way to put asterisks around a tablename!columname in a query, on
not...

Also, how do you do a Like statement in a DLookup?

More info...

What I'm trying to do is execute a query that lists records from one table
(table A) using information in another table (table B). The information in
table A contains more descriptive text than in table B, so I need to be able
to do a Like *.* in order to get an accurate list from table A.

example: Table A contains an item, "Imbued Blackened Iron Maul" and Table B
contains, "Blackened Iron Maul"

I need to be able to pull the record from A since it matches using the Like
*.* criteria.

Thanks for you help


:

you need to add "the other table" into the query ro use a DLookup to
explicitly return a single value back that can be used in the Like statement

It's been awhile since I've used SQL and I know there is a way to do
this...

I'm trying to create a Select query that selects items, using "Like *.*'
from a table using a field from another table.

In Design View, Access is formatting the criteria as
"*

.[columnname]*" and this, of course, doesn't work very well.

There is a way to do a wildcard for this situation I just can't remember
and
wading through all the available resources is proving fruitless.

I sure could use some help with this

 

[Guest]

Thanks for that piece of info. Concatenating the * around the field name was
the trick.

Here is how my query looked: Select TableA.* From TableA, TableB Where
TableA.FieldName Like "*" & TableB.FieldName & "*"

And this only returned all the rows that matched from TableA.

Thanks for you help, much appreciated!

Zel

so bring both into the query as suggested and use

Select TableA.*, TableB.* from TableA, TableB where TableA.FieldA like "*" &
TableB.FieldA & "*"

this does mean you may get more results that expected. Unsing your example
you get a single match, but if there was a record containing "Another
Blackened Iron Maul" there would be 2 results. (2 x 1). The more entries the
quicker this multiplies out. ie 2 records in each table would give 4 rows
etc. . . .

That may work but, it's not how I remember being able to do the query.
There
is some way to put asterisks around a tablename!columname in a query, on
not...

Also, how do you do a Like statement in a DLookup?

More info...

What I'm trying to do is execute a query that lists records from one table
(table A) using information in another table (table B). The information
in
table A contains more descriptive text than in table B, so I need to be
able
to do a Like *.* in order to get an accurate list from table A.

example: Table A contains an item, "Imbued Blackened Iron Maul" and Table
B
contains, "Blackened Iron Maul"

I need to be able to pull the record from A since it matches using the
Like
*.* criteria.

Thanks for you help

you need to add "the other table" into the query ro use a DLookup to
explicitly return a single value back that can be used in the Like
statement


It's been awhile since I've used SQL and I know there is a way to do
this...

I'm trying to create a Select query that selects items, using "Like
*.*'
from a table using a field from another table.

In Design View, Access is formatting the criteria as
"*

.[columnname]*" and this, of course, doesn't work very well.

There is a way to do a wildcard for this situation I just can't
remember
and
wading through all the available resources is proving fruitless.

I sure could use some help with this