Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2003
    Posts
    300

    Unanswered: Return Results from multiple fileds

    I have a table with 10 fields, I can write a simple query to ask for user parameter, say the date or a name and the query will return the results from that one field. but , if I want to search on a name or number in more than one field, how can I do that?

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The question implies a design normalization issue, as you may have repeating fields. That said, the solution in SQL would be:

    ...WHERE Field1 = [Enter whatever] OR Field2 = [Enter whatever]

    In the design grid, you'd have the same parameter on a different row (same row = "AND", different row = "OR").
    Paul

  3. #3
    Join Date
    Nov 2003
    Posts
    300
    Thanks for the reply, but that was not exactly what I was asking. I am asking how to accept a user parameter and pass that to the other 5 or ten fields without repeating the question to the user.

    EX. where .. [Enter ID] -- this is for field 1
    where .. [Enter ID] -- this is for field 2
    where .. [Enter ID] -- this is for field 3
    where .. [Enter ID] -- this is for field 4 .. etc.

    So I am asking the user to put in the SAME ID only once and have ACESS pass the parameter to the other where statements, say '192' and have it search for that ID in fields, 1, 2, 3, 4... etc. and return records that match.

    I have no control over how this data is stored ... I did not design the tables..
    Thanks!
    Last edited by databasemon; 09-20-10 at 10:53. Reason: forget to mention

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    don't use a parameter, instead set the value by running the query from VBA
    either build the query in VBA

    eg
    [code]strSQL= "select my, column, list from my Table"
    strSQl= strSQL= & " WHERE 1=1" 'this is a bit of SQL trickery to make certain it alwasy returns something
    if <a logical test = true> then strSQl= strSQL= & " AND aColumn=" anumericValue
    if <another logical test = true> then strSQl= strSQL= & " AND bColumn='" atextValue & "'"
    ..and so on
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2003
    Posts
    300
    Thanks, I am not great in VBA, so I will need some examples .. in detail

    I just want to be sure this is the answer to the question I am aksing.. so, when the user runs a report or query it asks them for the ID.. they type in 192 only once and ACCESS looks for 192 in field one, if it finds it, returns the record, if not, then goes to field two, if it finds it, returns the record... etc.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you are looking for the specified value in anyone of n columns?

    you could pull the parameter in form a form
    set up a form to vlaidate the parameters
    then use that value int he query

    say you from is called frmParameters
    and the control on that form with the valyue you want to use is called myControl

    then
    SELECT my, column, list, FROM MyTable
    WHERE AColumn = frmParameters.MyControl.Value
    OR BColumn = frmParameters.MyControl.Value
    ....
    OR nColumn = frmParameters.MyControl.Value
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Quote Originally Posted by databasemon View Post
    I am asking how to accept a user parameter and pass that to the other 5 or ten fields without repeating the question to the user.
    Did you try what I posted? As long as the text within the brackets is the same, the user will only be asked once.
    Paul

  8. #8
    Join Date
    Nov 2003
    Posts
    300
    Thanks healdem and pbaldy, pbaldy especially for your persistence.. it's the simple things staring right at me! Got it to work using your method .. did not know it was that simple-- works perfect!

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No problemo!
    Paul

Posting Permissions

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