Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with Parameter Query on ID Field? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    There are 485 records available

    This works OK, returns 8 records
    <pre>SELECT *
    FROM Claims INNER JOIN Members ON Claims.FKMemberID = Members.MemberID
    WHERE (((Claims.FKMemberID)=1598))
    ORDER BY Claims.MemberName;
    </pre>



    This works OK, returns 8 records when I enter 1598
    <pre>SELECT *
    FROM Claims INNER JOIN Members ON Claims.FKMemberID = Members.MemberID
    WHERE (((Claims.FKMemberID)=[Enter Member ID]))
    ORDER BY Claims.MemberName;
    </pre>



    This does not work OK, returns 27 records (see following) when I enter 1598
    Reuurns 485 when I click OK
    <pre>SELECT *
    FROM Claims INNER JOIN Members ON Claims.FKMemberID = Members.MemberID
    WHERE (((Claims.FKMemberID)>=IIf([Enter Member ID]>0,[Enter Member ID],0)))
    ORDER BY Claims.MemberName;
    </pre>


    What am I doing wrong?

    Thanks, John

    FKMemberID
    1600
    1605
    1605
    1602
    1599
    1599
    1599
    1599
    1599
    1604
    1604
    1604
    1604
    1607
    1607
    1598
    1598
    1598
    1598
    1598
    1598
    1598
    1598
    1606
    1606
    1601
    1601

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

    Re: Help with Parameter Query on ID Field? (a2k (9.0.6926) SP-3 Jet 4.0 SP

    Access does exacty what you ask from it. You have "greater than or equal to" in your last condition, so if you enter 1598, Access correctly returns all records with a FKMemberID greater than or equal to 1598, not just those equal to 1598.

    Try changing the WHERE part to

    WHERE Claims.FKMemberID>[Enter Member ID] Or [Enter Member ID] Is Null

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Parameter Query on ID Field? (a2k (9.0.6926) SP-3 Jet 4.0 SP

    Thanks, John

Posting Permissions

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