asked on

Modify Sql Query To Make Search Less Literal

I need to modify the following Sql query so that it will find full or partial names in one field; example:
It should find John Smith, John D Smith, Smith, John or Smi

SELECT WarrantLog.Item, WarrantLog.Name, WarrantLog.MI, WarrantLog.Race, WarrantLog.Sex, WarrantLog.SSN, WarrantLog.Offence, WarrantLog.Agency, WarrantLog.[Case No], WarrantLog.Served, WarrantLog.Returned, WarrantLog.[Enter Info], WarrantLog.Comment
FROM WarrantLog
WHERE (((WarrantLog.Name) Like [Forms]![WarrantSearchForm]![Name]) AND ((WarrantLog.Served)=No) AND ((WarrantLog.Returned)=No));

Open in new window

the_b1ackfox

SELECT WarrantLog.Item, WarrantLog.Name, WarrantLog.MI, WarrantLog.Race, WarrantLog.Sex, WarrantLog.SSN, WarrantLog.Offence, WarrantLog.Agency, WarrantLog.[Case No], WarrantLog.Served, WarrantLog.Returned, WarrantLog.[Enter Info], WarrantLog.Comment
FROM WarrantLog

WHERE (((WarrantLog.Name) Like %John%) AND ((WarrantLog.Served)=No) AND ((WarrantLog.Returned)=No));

That will find all John variants and replacing john with smit will find all smith variants

mickeyshelley1

ASKER

The search criteria will be coming from [Forms]![WarrantSearchForm]![Name]) which is a textbox could you make that modification please

The search criteria will be coming from [Forms]![WarrantSearchForm]![Name])

Open in new window

mickeyshelley1

ASKER

so it could be any name, the objective is to make the search more forgiving

the_b1ackfox

ok, I am guessing here... but maybe %[Forms]![WarrantSearchForm]![Name]%

mickeyshelley1

ASKER

I keep running into Syntax Errors

the_b1ackfox

Your text box is coming from visual basic right?

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial