Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2008
    Posts
    5

    Talking Unanswered: WHERE x = x except if x = NULL then show all records

    Hi guys, hope someone can help... I am pulling records from a simple access DB where account number of login = account number of payments table then show record. However i am trying to achieve if the account number = "NULL ENTRY" then show all records in the Payments table

    here is my code so far

    "SELECT * FROM Payments WHERE Payments.AccNumber = " & AccNum & " ORDER BY DateEntered"

    Thanks is advance
    Last edited by Chevron22; 04-17-08 at 08:00.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT *
    FROM   accounts
    WHERE (acctNum = @param
     OR    @param IS NULL)
    ??
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    WHERE nz(@param, acctNum) = acctNum
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Apr 2008
    Posts
    5

    Wink

    Quote Originally Posted by Teddy
    WHERE nz(@param, acctNum) = acctNum
    What does the nz stand for here?

    cheers

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    NZ = Non-Zero

    It converts NULLs into a specified value; in this case, if @param is null the expression essentially becomes
    Code:
    WHERE acctNum = acctNum
    George
    Home | Blog

Posting Permissions

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