Parameter help in a union query (1 Viewer)

Jmsteph

Registered User.
Local time
Today, 09:32
Joined
Dec 2, 2008
Messages
28
I am using a union query to pull policy numbers from two tables together for a search. What I would like to accomplish is to add a wild card to is so that the parameter for searching by the name will look within the field. In a standard query, I'd just set the criteria for the name to be Like "*" & [What is the Clients Last Name, First Name?] & "*", but in SQL, this doesn't seem to work.

SELECT [PolNum], [Status], [Plan_Name], [SSN], [Name]
FROM dbo_SiouxFalls_POLICYLIST
WHERE (([Name] = [What is the Clients Last Name, First Name?] ) AND ([Role] ="10"))
UNION SELECT dbo_T_POLICY.pm_policy_number, dbo_T_POLICY.pm_status_ind, dbo_T_POLICY.pm_company_code, dbo_RoleClient.CL_TIN, dbo_RoleClient.[Last,FirstName]
FROM dbo_RoleClient INNER JOIN dbo_T_POLICY ON dbo_RoleClient.RIR_ROLE_POLICY = dbo_T_POLICY.pm_policy_number
WHERE (((dbo_RoleClient.[Last,FirstName]) = [What is the Clients Last Name, First Name?] ) AND ((dbo_RoleClient.RIR_ROLE_ROLE)="00"));

 

vbaInet

AWF VIP
Local time
Today, 14:32
Joined
Jan 22, 2010
Messages
26,374
Just before you proceed, you do realise that your query will be read-only?
 

Taruz

Registered User.
Local time
Today, 14:32
Joined
Apr 10, 2009
Messages
168
Hi..

You want something like this..: ;)


Code:
select * from 
(
    select 
                 [PolNum], 
                 [Status], 
                 [Plan_Name], 
                 [SSN], 
                 [Name] as trz
    from dbo_SiouxFalls_POLICYLIST
    where [Role] ="10"

    union

    select 
                 z.pm_policy_number, 
                 z.pm_status_ind, 
                 z.pm_company_code, 
                 y.CL_TIN, 
                 y.[Last,FirstName]
    from dbo_RoleClient as y 
                   inner join 
             dbo_T_POLICY as z on y.RIR_ROLE_POLICY = z.pm_policy_number
    where y.RIR_ROLE_ROLE="00"
) as tt

where trz Like "*" & [What is the Clients Last Name, First Name?] & "*"
 

Users who are viewing this thread

Top Bottom