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

Topic

New Reply

There are 485 records available

This works OK, returns 8 records

SELECT *
FROM Claims INNER JOIN Members ON Claims.FKMemberID = Members.MemberID
WHERE (((Claims.FKMemberID)=1598))
ORDER BY Claims.MemberName;

This works OK, returns 8 records when I enter 1598

SELECT *
FROM Claims INNER JOIN Members ON Claims.FKMemberID = Members.MemberID
WHERE (((Claims.FKMemberID)=[Enter Member ID]))
ORDER BY Claims.MemberName;

This does not work OK, returns 27 records (see following) when I enter 1598
Reuurns 485 when I click OK

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;

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

Reply | Quote

Replies

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

Reply | Quote

Thanks, John

Reply | Quote