0

So, I have an Access database where full names of patients are stored in a single field, ala "John Henry Michaels". I would like to be able get a name from the user search for it in the database. Now, here I found this

WHERE " " & tblEmployee.FullName & " " Like "* " & tblNames.Name & " *"

which is almost what I want. The only problem is that if the user doesn't know the middle name and enters "John Michaels", then "John Henry Michaels" will not show up. Is there a way to search the field for any part of the user input?

Community
  • 1
  • 1
Dieterlan
  • 13
  • 4

1 Answers1

0

For this particular scenario I would separate the user input and put it in the query as 2 parts (You may need to set up a loop to separate the users input if there is a chance they may enter a name with more than 2 parts).

You would end up with a query that looks like this:

WHERE " & tblEmployee.FullName & " LIKE '* " & NamePt1 & " *' AND " & tblEmployee.FullName & " LIKE '* " & NamePt2 & " *'"

Alternatively, you may want to normalize your database and separate the name into 3 parts, as that is the standardized database design.

Michael Russo
  • 442
  • 6
  • 14
  • Unfortunately, due to the nature of the database there is no set limit to the number of names a person can have. "Michael Daniel Alexander Davis Peterson Johnson" is perfectly valid. I also can't guarantee two parts being entered, so I'll look into a loop – Dieterlan Jul 21 '16 at 19:19