Query covering 2 fields

[acrowe97]

The query I'm making is going to be used in a report, showing what company has what keys related to a specific office suite. The issue is some companies use a doing business as (DBA) title instead of the company name on the lease. I need to have the query look up partial names of either the company or DBA name so the data displays. Any suggestions?

[acrowe97]

I tried using the Like * & " " & * search in both the Company field and DBA field at the same time but it just gives me two search boxes and no data is displayed on the query.

[orange]

Show us the SQL view of you query.

[acrowe97]

SELECT Tenants.CompanyName, Tenants.DBA, Suites.SuiteNumber, [Suite Key List].KeyNumber, [Suite Key List].Quantity, [Suite Key List].Notes, [Suite Key List].MapKeyAtt, [Suite Key List].Security, [Suite Key List].SecurityCodeON, [Suite Key List].SecurityCodeOFF
FROM Tenants INNER JOIN ((Leases INNER JOIN Suites ON Leases.LeaseID = Suites.LeaseID) INNER JOIN [Suite Key List] ON Suites.SuiteNumber = [Suite Key List].SuiteNumber) ON Tenants.TenantID = Leases.TenantID
WHERE (((Tenants.CompanyName) Like "*" & [Enter a tenant/company:] & "*"));

[orange]

You could try this query just to see if you get back the data/records you expect.

PARAMETERS pSearchName text(20);

SELECT Tenants.CompanyName
    ,Tenants.DBA
    ,Suites.SuiteNumber
    ,[Suite Key List].KeyNumber
    ,[Suite Key List].Quantity
    ,[Suite Key List].Notes
    ,[Suite Key List].MapKeyAtt
    ,[Suite Key List].Security
    ,[Suite Key List].SecurityCodeON
    ,[Suite Key List].SecurityCodeOFF
FROM Tenants
INNER JOIN (
    (
        Leases INNER JOIN Suites ON Leases.LeaseID = Suites.LeaseID
        ) INNER JOIN [Suite Key List] ON Suites.SuiteNumber = [Suite Key List].SuiteNumber
    ) ON Tenants.TenantID = Leases.TenantID
WHERE (Tenants.CompanyName LIKE "*" & pSearchName & "*")
    OR (Tenants.DBA LIKE "*" & pSearchName & "*");

[acrowe97]

It works!! Thank you so much.