Nz function in select statment

G

Guest

I'm trying to use an Nz function to deal with a null field. I'm using a
selet statment to pull the records. I'm getting a syntax error for my Nz
portion. Do I have it set up wrong? Can I even use it in this manner?
strSQL = "SELECT a.UNIQUEID, a.ID, a.SUBSCRIBERID, a.RETAILERID,
a.STORENUMBER, a.USERID, " & _
"a.VISITDATE, a.VENDORID, a.POPID, a. Nz([NOTES],N/A) a.PARTNUMBER,
a.SHIPTO, a.QTY, a.ORDERPLACED, " & _
"b.SHIPFROM, b.SHIPTOID, b.DESCRIPTION " & _
"FROM UNISOLINC_TBLPOPORDERS a " & _
"INNER JOIN UNISOLINC_TBLPOPLIST b " & _
"ON a.POPID = b.UNIQUEID " & _
"WHERE (((a.SUBSCRIBERID) = 13 OR (a.SUBSCRIBERID) = 37 OR
(a.SUBSCRIBERID) = 61) " & _
"AND ((b.SHIPFROM) = 'Vendor') AND ((a.ORDERPLACED) = 0) AND " & _
"((a.RECEIVED) > #2/1/2005#) and ((a.RECEIVED) < #" & dtEND & "#));"
 
D

Douglas J. Steele

Since N/A isn't a numeric value, it needs to be in quotes. Notice how I've
used two double quotes in a row: that's how you represent a quote within a
quoted string.

strSQL = "SELECT a.UNIQUEID, a.ID, a.SUBSCRIBERID, a.RETAILERID,
a.STORENUMBER, a.USERID, " & _
"a.VISITDATE, a.VENDORID, a.POPID, a. Nz([NOTES],""N/A"")
a.PARTNUMBER,
a.SHIPTO, a.QTY, a.ORDERPLACED, " & _
"b.SHIPFROM, b.SHIPTOID, b.DESCRIPTION " & _
"FROM UNISOLINC_TBLPOPORDERS a " & _
"INNER JOIN UNISOLINC_TBLPOPLIST b " & _
"ON a.POPID = b.UNIQUEID " & _
"WHERE (((a.SUBSCRIBERID) = 13 OR (a.SUBSCRIBERID) = 37 OR
(a.SUBSCRIBERID) = 61) " & _
"AND ((b.SHIPFROM) = 'Vendor') AND ((a.ORDERPLACED) = 0) AND " & _
"((a.RECEIVED) > #2/1/2005#) and ((a.RECEIVED) < #" & dtEND &
"#));"

And while not a big deal, you could replace

"WHERE (((a.SUBSCRIBERID) = 13 OR (a.SUBSCRIBERID) = 37 OR
(a.SUBSCRIBERID) = 61) " & _

with

"WHERE (((a.SUBSCRIBERID) IN (13, 37, 61)) " & _
 
A

Allen Browne

Add quotes around the text value that you want to substitue for null:
Nz([NOTES],'N/A')
 
Top