WHERE x = x except if x = NULL then show all records

Chevron22 · #1 (**permalink**) 04-17-08, 02:38

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

gvee · #2 (**permalink**) 04-17-08, 11:50

Code:

SELECT *
FROM   accounts
WHERE (acctNum = @param
 OR    @param IS NULL)

??

Teddy · #3 (**permalink**) 04-22-08, 15:17

WHERE nz(@param, acctNum) = acctNum

Chevron22 · #4 (**permalink**) 04-23-08, 00:50

Quote:

Originally Posted by Teddy

WHERE nz(@param, acctNum) = acctNum

What does the nz stand for here?

cheers

gvee · #5 (**permalink**) 04-23-08, 07:22

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

Internet Services

Web Development

Digital Marketing

Consumer Tech