Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2002
    Posts
    8

    Unanswered: Mutliple Field Parameter Query

    Has anyone attempted to create a parameter query using a form with multiple fields. What I currently have is a form with say 20 fields. I would like to be able to run a query on a table that searches for user input in any of the fields on the form (sort of like the "Find" command). Below is sample code that works for multiple fields but the user input has to match the record(s) exactly.

    Select *
    From table
    Where (((table.field) Like '*' & [Forms]![queryform]![field] & '*')) AND ((table.field) Like '*' & [Forms]![queryform]![field] & '*'));

    For example if the user is looking for a record that has a name field but the user isn't sure what the exact name is they would enter say "Rob" and the query would return all records with "Rob" in the field which may include "Rob", "Robert", "Roberta", "Strob" etc. Second say the user entered that same info in the name field but also entered info into say 5 more fields such as last name, birthdate, etc. I would want the query to return all records that 1) where like the entry in the name field 2) where like the entry in the last name field 3) where like the entry in the birthdate field etc.

    Hope this is clear. Thanks

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Build you SQL string in a For Each loop. Going through all your text boxes in a loop, if a box is blank ignore it, otherwise add the field and its contents to the SQL string, such as

    Code:
    "... And FieldX Like '*" & Me.txtFieldX & "*'" ...
    Hope This Helps,
    Sam

  3. #3
    Join Date
    May 2005
    Posts
    1,191
    First off, I personally am not a real big fan of parameter queries, I prefer to build them in VBA. But if you're dead set on this, I think it can work.

    Secondly, have a look at this post in the code bank and see if it helps you out at all. Let us know how you get on.
    Me.Geek = True

  4. #4
    Join Date
    Feb 2002
    Posts
    8

    Thank You

    Thanks nckdryr and Sam.....I will check out your references and post the outcome....I hope

Posting Permissions

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