Like Statement comparison problem

[Guest]

I have a query that I am trying to use to return names and addresses that
match partial or full to another field. The statements I am using are:

SELECT [tbl DP 1].[HP Name], denial2.NAME1 AS [MK Name], denial2.STREET1 AS
[MK Street], [tbl DP 1].[Address 1] AS [JDE Street Address], denial2.STREET2
AS [MK Street2], [tbl DP 1].[Address 2] AS [JDE Street2], denial2.CITY AS [MK
City], [tbl DP 1].City AS [JDE City], denial2.STATE AS [MK ST], [tbl DP
1].State AS [JDE ST], [tbl DP 1].[HP#], [tbl DP 1].GainDate, [tbl DP 1].[RCO
#], [tbl DP 1].Status, [tbl DP 1].[HPSR ID]
FROM [tbl DP 1], denial2
WHERE ((([denial2]![STREET1]) Like "*" & [tbl DP 1]![Address 1] & "*"));

**********************

SELECT [tbl DP 1].[HP Name] AS [JDE HP Name], denial2.NAME1 AS [MK DB Name],
denial2.STREET1 AS [Mk Street1], denial2.STREET2 AS [Mk Street2],
denial2.CITY AS [MK City], denial2.STATE AS [MK St], [tbl DP 1].GainDate,
[tbl DP 1].[HP#], [tbl DP 1].[HPSR ID], [tbl DP 1].[RCO #], [tbl DP
1].Status, [tbl DP 1].[Address 1] AS [JDE Address], [tbl DP 1].[Address 2] AS
[JDE Address2], [tbl DP 1].City AS [JDE City], [tbl DP 1].State AS [JDE St],
[tbl DP 1].Zip AS [JDE Zip], [tbl DP 1].[Class Code], [tbl DP 1].Language
FROM [tbl DP 1], denial2
WHERE ((([denial2]![NAME1]) Like "*" & [tbl DP 1]![HP Name] & "*"));

The values are only returning if they exactly match or if I test it having
one word in the field I am trying to match. When there is a complete address
or name (first last) and I try to match a partial, it returns nothing. Is
there a way to write this query so that partial will match anything contained
in the field

Example: [tbl denial2].[NAME1] = Smith John and in [tbl DP1].[HP Name] =
Smith Johnathan would come back not matching, howeverif both the names said
Smith and Smith they do come back as matches.

Any ideas you can provide so I get matches back when partially matching or
completely matching would be great. There are also commas and some special
characters in the [tbl denial2].[NAME1] field but I am running a query to
remove these first. Thanks in advance for your help.

 

[Wolfgang Kais]

Hello Brad.

I have a query that I am trying to use to return names and addresses
that match partial or full to another field. The statements I am using
are: [Snip]
WHERE ((([denial2]![STREET1]) Like "*" & [tbl DP 1]![Address 1] & "*"));

The values are only returning if they exactly match or if I test it
having one word in the field I am trying to match. When there is a
complete address or name (first last) and I try to match a partial, it
returns nothing. Is there a way to write this query so that partial
will match anything contained in the field

Example: [tbl denial2].[NAME1] = Smith John and in
[tbl DP1].[HP Name] = Smith Johnathan would come back not
matching, howeverif both the names said Smith and Smith they do
come back as matches.

Your query checks whether "Smith John" Like "*Smith Johnathan*",
while the reverse is true. Change the "where"-clause to:
WHERE ((([tbl DP 1]![Address 1]) Like "*" & [denial2]![STREET1] & "*"));