NZ

[Fensbo]

Hi there, I can't seem to get this to work CPR NZ: NZ([Bil_CPR];0) in a query. It should return 0 (zero) if the field [Bil_CPR] is null...?!

What am I doing wrong?!

 

[RoyVidar]

But - what does it return?

One guess, the field isn't Null but contains ZLS (zero length string ""), which isn't trapped by NZ.

[tt]iif(trim$(Bil_CPR & "")="";0;Bil_CPR)[/tt]

Roy-Vidar

 

[jsteph]

I'm assuming the ; in NZ is a typo, and that you have a comma instead?

Otherwise Royvidar is on the right track, but do this:
Nz(trim$("" & [fieldname]),0)

Trim$ returns a string. I put the concat of the "" in to force any NULL to an empty string.
--Jim

 

[PHV]

Jim, "" & [fieldname] will NEVER return null.

 

[Fensbo]

Hi there,

No it's not a typo, it will not accept "," only ";"...?!

/Christian

 

[PHV]

It's standard behaviour on some european package.

 

[RoyVidar]

Here's a thrad with a bit of explanation on the semicolon vs comma thread702-733634 thingie

Roy-Vidar

 

[Fensbo]

Okay, I'm using Danish standard...

)

 

[jsteph]

phv,frensbo,
My mistake--I meant to put the trim$("" & [fieldname]) in an IIF() statement and NOT Nz(). I'm well aware that it wont' return null, that's the whole reason for concatenating the "".

Sorry for the confusion.
--Jim