string two columns together and use the string in another qry

[Crystal via AccessMonster.com]

Can I string two columns (lastname, firstname) together and use the string in
a query to query a fullname column? I have a table with two columns: [last
name] and [first name]. I need to string them together with words “LIKE” and
“OR” (LIKE [lastname]*[firstname]* OR LIKE… ) so that I don’t have to type
the names one by one to query a full name column. The query would be like :
LIKE SMITH*JOHN* OR LIKE WHITE*MATT* OR LIKE …

Can I do this?

Thank you in advance.

 

[Ken Snell \(MVP\)]

Something like this, perhaps:

SELECT *
FROM TableName
WHERE [lastname] & [firstname] LIKE "*MyEntry*";

If this isn't what you're seeking, show us some sample data and how you want
to be able to search the data. What you want to do is very feasible.

 

[Crystal via AccessMonster.com]

Thank you. Here they are: I have two table: FullNames and SeparateNames. I
need to retrieve info from FullNames tbl based on SeparateNames table.

FullNames tbl
NAME (This is one field)
Smith, John D.
Kuwit, Gout (M.D.)
Good, Liana M.
Tele, Bom O.
Wolf, Terry

SeparateNames tbl
Lnm Fnm (two fields)
Smith John
Kuwit Gout
Good Liana
Tele Bom
Wolf Terry

I need the qry to be like:

SELECT * FROM [FULLNAMES]
WHERE NAME LIKE SMITH*JOHN* OR NAME LIKE KUWIT*GOUT* OR NAME LIKE…But I hope
I don’t have to type out the where clause. Is there a way to accomplish this?
Thank you very much!!

Something like this, perhaps:

SELECT *
FROM TableName
WHERE [lastname] & [firstname] LIKE "*MyEntry*";

If this isn't what you're seeking, show us some sample data and how you want
to be able to search the data. What you want to do is very feasible.

Can I string two columns (lastname, firstname) together and use the string
in

[quoted text clipped - 9 lines]

Thank you in advance.

 

[John Spencer]

You can do this with a non-equi join on the two tables. This type of join
cannot be built in the query grid, but must be entered in the SQL (text)
window.

SELECT F.[Name]
FROM FullNames as F INNER JOIN SeparateNames As S
ON F.Name LIKE SLnm & ", " & S.Fnm & "*"

You can vary the join criteria as needed. For instance, if you weren't sure
about the separator between last and first names but wanted to be sure there
was at least one space between the two parts.
ON F.Name LIKE SLnm & "* *" & S.Fnm & "*"

Thank you. Here they are: I have two table: FullNames and SeparateNames. I
need to retrieve info from FullNames tbl based on SeparateNames table.

FullNames tbl
NAME (This is one field)
Smith, John D.
Kuwit, Gout (M.D.)
Good, Liana M.
Tele, Bom O.
Wolf, Terry

SeparateNames tbl
Lnm Fnm (two fields)
Smith John
Kuwit Gout
Good Liana
Tele Bom
Wolf Terry

I need the qry to be like:

SELECT * FROM [FULLNAMES]
WHERE NAME LIKE SMITH*JOHN* OR NAME LIKE KUWIT*GOUT* OR NAME LIKE.But I
hope
I don't have to type out the where clause. Is there a way to accomplish
this?
Thank you very much!!

Something like this, perhaps:

SELECT *
FROM TableName
WHERE [lastname] & [firstname] LIKE "*MyEntry*";

If this isn't what you're seeking, show us some sample data and how you
want
to be able to search the data. What you want to do is very feasible.

Can I string two columns (lastname, firstname) together and use the
string
in

[quoted text clipped - 9 lines]

Thank you in advance.

 

[coedxiao via AccessMonster.com]

Thank you so much. It works wonder!

You can do this with a non-equi join on the two tables. This type of join
cannot be built in the query grid, but must be entered in the SQL (text)
window.

SELECT F.[Name]
FROM FullNames as F INNER JOIN SeparateNames As S
ON F.Name LIKE SLnm & ", " & S.Fnm & "*"

You can vary the join criteria as needed. For instance, if you weren't sure
about the separator between last and first names but wanted to be sure there
was at least one space between the two parts.
ON F.Name LIKE SLnm & "* *" & S.Fnm & "*"

Thank you. Here they are: I have two table: FullNames and SeparateNames. I
need to retrieve info from FullNames tbl based on SeparateNames table.

[quoted text clipped - 39 lines]