How to make Nulls Blank 2

[RobNotKnowing]

Hi,,,,

I am trying to replace the Nulls with blanks in my query output. Does any1 know how?

 

[Melagan]

The Nz() function is probably what you're looking for.

Null values to zero: Nz([YourField], 0)
Null values to zero-length string: Nz([YourField, "")


~Melagan
______
"It's never too late to become what you might have been.

 

[RobNotKnowing]

due I put that after the word select or after my inner join?

 

[Melagan]

Go ahead and paste your current SQL and tell me what specific field(s) you want to handle Nulls with.

~Melagan
______
"It's never too late to become what you might have been.

 

[RobNotKnowing]

select loan_no_i,loan#,prevloan#,invloan#,fname,lname,
add1,add2,city,state,zip,upb,daysdelq,gracedays,nextdue,totaldelq,
intrate,propadd1,propadd2,propcity,propstate,propzip,cg.homephone,cg.workphone,
[b2 homephone],[b2 workphone],perdiem,lastrecd,loandate,loanamt,investor,
pool,ssn,[b2 ssn],b2fname,B2Lname,[b2 add1],[b2 add2],[b2 city],
[b2 state],[B2 Zip],pipmt,impoundamt,
monthsdelq,statement,pidelq,suspense,nsfdue,[latechgdue type],unpartial,
[Late Fee Perct],[Fixed Late],LienPosition,CollCode,Status,[Status Date]
[Maturity Date],FirstPmt,[LateChgDue Amt],Warning,Legal,
[Mail Stop],BKChap,BKCase#,OptOut,AdvanceAmt,
EscrowBal,ARMLoan,PrePetSuspense,StipSuspense,PostPetSuspense,
c.FollowUpDt,c.PromisedByDate

from rtr_cgallloans as cg
inner join contact as c on c.loanid = cg.loan_no_i

where status = '1'
and (legal not in ('dsc7all','dsc7par','dsc13all','dsc13par','chap7','chap11',
'chap12','chap13','fcl','bk/fc','dsc/fc','bkpend', 'REO'))
or legal is null
and cg.homephone not like '(000%'
and cg.homephone not like '(111%'
and cg.homephone not like '(222%'
and cg.homephone not like '(333%'
and cg.homephone is not null


I want the nulls to be shown as blanks in cg.homephone,cg.workphone,
[b2 homephone],[b2 workphone]

 

[Melagan]

select loan_no_i,loan#,prevloan#,invloan#,fname,lname,
add1,add2,city,state,zip,upb,daysdelq,gracedays,nextdue,totaldelq,
intrate,propadd1,propadd2,propcity,propstate,propzip, [COLOR=red]Nz(cg.homephone, "") as cgHomePhone, Nz(cg.workphone, "") as cgWorkPhone, Nz([b2 homephone], "") as b2HomePhone, Nz([b2 workphone], "") as b2WorkPhone[/color], perdiem,lastrecd,loandate,loanamt,investor,
pool,ssn,[b2 ssn],b2fname,B2Lname,[b2 add1],[b2 add2],[b2 city],
[b2 state],[B2 Zip],pipmt,impoundamt,
monthsdelq,statement,pidelq,suspense,nsfdue,[latechgdue type],unpartial,
[Late Fee Perct],[Fixed Late],LienPosition,CollCode,Status,[Status Date]
[Maturity Date],FirstPmt,[LateChgDue Amt],Warning,Legal,
[Mail Stop],BKChap,BKCase#,OptOut,AdvanceAmt,
EscrowBal,ARMLoan,PrePetSuspense,StipSuspense,PostPetSuspense,
c.FollowUpDt,c.PromisedByDate

from rtr_cgallloans as cg
inner join contact as c on c.loanid = cg.loan_no_i

where status = '1'
and (legal not in ('dsc7all','dsc7par','dsc13all','dsc13par','chap7','chap11',
'chap12','chap13','fcl','bk/fc','dsc/fc','bkpend', 'REO'))
or legal is null
and cg.homephone not like '(000%'
and cg.homephone not like '(111%'
and cg.homephone not like '(222%'
and cg.homephone not like '(333%'
and cg.homephone is not null

~Melagan
______
"It's never too late to become what you might have been.

 

[RobNotKnowing]

Server: Msg 1038, Level 15, State 3, Line 3
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 195, Level 15, State 1, Line 3
'Nz' is not a recognized function name.
Server: Msg 1038, Level 15, State 1, Line 4
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 1038, Level 15, State 1, Line 4
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 1038, Level 15, State 1, Line 5
Cannot use empty object or column names. Use a single space if necessary.

 

[Melagan]

Are you querying a MySQL or SQL Server table? My solution should work for Jet (and only Jet)

~Melagan
______
"It's never too late to become what you might have been.

 

[RobNotKnowing]

sql server table

sorry for the misunderstanding.

 

[Melagan]

No sweat - I'm know for a fact that there are a lot of other programmers around here that know SQL Server; I, unfortunately, do not =\

~Melagan
______
"It's never too late to become what you might have been.

 

[PHV]

Doesn't sql server have a COALESCE function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cheerio]

SQL server generally uses single quotes where Access uses double quotes and uses IsNull() where Access uses Nz()

So

Nz(cg.homephone, "")

becomes

IsNull(cg.homephone, '')