Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 139
  • Last Modified:

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

0
mickeyshelley1
Asked:
mickeyshelley1
  • 3
  • 3
1 Solution
 
the_b1ackfoxCommented:
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
0
 
mickeyshelley1Author Commented:
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

0
 
mickeyshelley1Author Commented:
so it could be any name, the objective is to make the search more forgiving
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
the_b1ackfoxCommented:
ok, I am guessing here... but maybe %[Forms]![WarrantSearchForm]![Name]%
0
 
mickeyshelley1Author Commented:
I keep running into Syntax Errors
0
 
the_b1ackfoxCommented:
Your text box is coming from visual basic right?
0
 
SharathData EngineerCommented:
What is your database?

try the below query.

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));
 

or this one.

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 concat(concat('%',[Forms]![WarrantSearchForm]![Name]),'%'))
  AND ((WarrantLog.Served)=No) AND ((WarrantLog.Returned)=No));
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now