Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Nov 2004
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Criteria (access 2000)

    I have a query that uses an input parameter to find data. The parameter's are Adult last name, adult first name. child name. If I use Smith, I get all the smith's in the table, if I use Smith, John, Tyler I get all the smith's, all the John's and all the Tyler's. I would like only a record that matches all 3 criteria. How do i set that up in my qeury. If I only know one of the names I still need it to list all records with just the one name. Is there away to say if input parameter is blank than default to list all?

    Glenn

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query Criteria (access 2000)

    It would simplify finding the answer if you posted the SQL for your query.
    Charlotte

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query Criteria (access 2000)

    I assume you are using a parameter prompt something like [Enter Adult's Last Name:] in the criteria grid. If you want to have all three apply, you need to put the parameter prompts on the same line. If you put them on different lines you have an OR condition, but what you want is an AND condition. The other question is more complex - if you want to be able to put in just a First Name and a Child Name, the usual approach is to use a Form where the parameters are entered - that let's you manipulate the query in a more sophisticated fashion.
    Wendell

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Criteria (access 2000)

    Try a design as in the screenshot below. The parameters are in the Field row, and the Criteria row contains

    [FieldName] Or Is Null

    The field names in the screenshot are just examples, of course.

  5. #5
    New Lounger
    Join Date
    Nov 2004
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Criteria (access 2000)

    This helped, Thanks

Posting Permissions

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