Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parameter query - multiples (Access 2000)

    Hi everyone! I am attaching a small sample database, and wonder if you'd look at the multiple parameter query in it. I have always had trouble trying to ask two questions and know I am not asking correctly. If I choose to enter sales in the first prompt box, and skip the entry of position in the second prompt box, I should get results correct? If I choose to not enter sales in the first prompt but go ahead and enter clerk in the second prompt box, I should get correct results again, yes? I know it's my placement of the Is Null, and Is Not Null commands. Strategically placed it allows me to skip one, usually the second box, but not the first. Anyway...What I am after is to be allowed to skip either prompt I choose and get correct answers. Assuming there is a way.
    Thank you,
    Nannette
    Attached Files Attached Files
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  2. #2
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter query - multiples (Access 2000)

    <P ID="edit" class=small>(Edited by D Willett on 22-Sep-03 18:55. Error in zip file)</P>Nanette

    Is this what you were trying to achieve.??
    Attached Files Attached Files

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter query - multiples (Access 2000)

    No. It doesn't work either. I was trying to put [Enter Dept] (where I would enter "Sales"), then on the or line beside and below it I would enter: Like[Enter Dept]Is Null then for position I would put [Enter Position](where I would enter "Clerk"), and below it I would type: Like[Enter Position]Is Null
    And no matter how I attempted to arrange these, I can't make it work where I am allowed to put something in either box or not put something in either box...any other ideas: <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter query - multiples (Access 2000)

    Hi Nannette... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    I'd like to try and help, but I only have Access 97 at work... Could you save your sample database in the previous format and re-post it so that I can take a look??

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter query - multiples (Access 2000)

    don't know if this will work, but try it...
    Attached Files Attached Files
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  6. #6
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter query - multiples (Access 2000)

    Okay Nannette... I think I have it...

    Create a second query called "qryGetParameters" and enter the following in the SQL view....
    SELECT Nz([Enter a Dept],"[a-zA-Z]*") AS DeptFilter, Nz([Enter a Position],"[a-zA-Z]*") AS PosnFilter
    FROM [Staff Details];


    Then open your "multiple question parameter query" and enter the following in the SQL view for that one...
    SELECT DISTINCT [Staff Details].lngStaffNo, [Staff Details].LastName, [Staff Details].FirstName, [Staff Details].Dept, [Staff Details].Position, [Staff Details].Salary, [Staff Details].Birthdate, [Staff Details].UnionMem
    FROM [Staff Details], qryGetParameters
    WHERE ((([Staff Details].Dept) Like [DeptFilter]) AND (([Staff Details].Position) Like [PosnFilter]));


    With those queries in place, I get prompted for both field values...
    If I answer both... the query is filtered on both...
    If I answer one or the other... it is filtered on that one value (and anything in the other)
    If I answer neither... the filter includes anything beginning with alpha. characters in either field...

    Let me know if this gives you the results you want.... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    HTH

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter query - multiples (Access 2000)

    Alexya1

    WOW your good. That worked beautifully. I never thought of using a second query or the Nz. Your GREAT and I SALUTE you!!! <img src=/S/salute.gif border=0 alt=salute width=15 height=20> Thanks for all the help, and I really mean that. The powers that be.

    Nannette
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Parameter query - multiples (Access 2000)

    You should be able to do this with one query, try this:
    SELECT [Staff Details].lngStaffNo, [Staff Details].LastName, [Staff Details].FirstName, [Staff Details].Dept, [Staff Details].Dept, [Staff Details].Position, [Staff Details].Position, [Staff Details].Salary, [Staff Details].Birthdate, [Staff Details].UnionMem
    FROM [Staff Details]
    WHERE ((([Staff Details].Dept)=[Enter a Dept] Or [Enter a Dept] Is Null) AND (([Staff Details].Position)=[Enter a Position] Or [Enter a Position] Is Null));

Posting Permissions

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