Results 1 to 4 of 4

Thread: search db ?'s

  1. #1
    Join Date
    Feb 2013
    Posts
    2

    Unanswered: search db ?'s

    What would be the best approach to search a concatenated field. I combined patient first name and last name to one field "PatientName) Like "*" & [Forms]![Form6]![txtSearchCustomers] & "*" the form's data qryPatients from the patients table


    Any thoughts or direction?

    Scott

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what are you trying to search for?
    the fact that you have concatenated names could be irrelevant becuase the underlying columns can still be accessed. you dont' have to have a column in visibile in the query for it to be used in a where clause

    however there are implications on using a concatenated (derived) column.
    you cannot use indexes.
    if you do use the concatenated column in a where clause AND visible in the query then you are forcing the query to do the concatenation operation twice.

    so assuming you can index the columns affected and use tho
    se source columns
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2013
    Posts
    2
    It is a name column, as it is now the user can enter either first or last name; however, certain last names produce 100+ records. i am looking for a way to be able search last name and first at same time.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what I'd be tempted to do is break apart what the user has typed and search for what ever elements they have supplied or get the user to put the forenames in one control the surname in another control.. its easier for you
    if it were me I'd be tempted to accept whatever the user enters and then match it against both the forename and surname.

    the psuedo code would be
    accept the user input
    break it a part into words using the split functions
    build the match terms based on those words

    say the user types in John Smith
    then I'd want to break that into two words
    then build a where clause
    where forename like '%john%' or forename like '%smith%'
    or surname like '%john%' or surname like '%smith%'

    in an ideal world you could use something similar to match of a full text search, but as far as Im aware these are not available
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •