NZ, IIf, IsNull---not working for me

[Cita]

Ok, I'm trying to do something like this:

If [Type] is null, return MISC; if not, return [Type]...

I have tried

NZ([Type],"MISC")
IIf(IsNull([Type]),"MISC", [Type])

I've even tried using Trim, just in case the [Type] field had spaces I
didn't know about.

Any other suggestions?

Thanks in advance!

 

[Stefan Hoffmann]

hi,

NZ([Type],"MISC")

If this doesn't work, then [Type] doesn't contain NULL.

I've even tried using Trim, just in case the [Type] field had spaces I
didn't know about.

[Type] is a text field?

Any other suggestions?

IIf(Len(Trim(Nz([Type],"")))=0,"Misc",[Type])


mfG
--> stefan <--

 

[Wayne-I-M]

SomeName: IIf(IsNull([TableName]![Type]),"MISC",[TableName]![Type])


SELECT IIf(IsNull([TableName]![Type]),"MISC",[TableName]![Type]) AS SomeName
FROM TableName;


Can't see anything wrong with what you have
"Should" work for you

 

[Bob Barnes]

If you have a zero-length string in the Field, try...
IIf(Len([Type]=0),"MISC", [Type])...
but if a zero-length string, [Type] will return that,
which will look like a Null, but isn't.

HTH - Bob

 

[Cita]

The last formula here worked...I don't understand it but it made me happy!

Thanks again!

hi,

NZ([Type],"MISC")

If this doesn't work, then [Type] doesn't contain NULL.

I've even tried using Trim, just in case the [Type] field had spaces I
didn't know about.

[Type] is a text field?

Any other suggestions?

IIf(Len(Trim(Nz([Type],"")))=0,"Misc",[Type])


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

The last formula here worked...I don't understand it but it made me happy!

IIf(Len(Trim(Nz([Type],"")))=0,"Misc",[Type])

Nz() returns for a NULL value an empty string.
Trim() removes (white) spaces from strings.
Len() returns the length of that string.

Create a query with that SQL to visualize it:

SELECT
[Type] AS Value,
"'" & [Type] & "'" AS QuotedValue,
Nz([Type],"") AS EmptyString,
Trim(Nz([Type],"")) AS Trimmed,
Len(Trim(Nz([Type],""))) AS Length
FROM yourTable

mfG
--> stefan <--