nz Left function #Error

G

Gabby Girl

How do I eliminate #Error when using Left Function where some fields are null ?
Right now I'm using =Left$([FuelCapacity],4) which works fine on the fields
that are not null, but I get #Error on the null fields. I've tried using
=nz(Left$([FuelCapacity],4)) but still get the #Error. (FuelCapacity is a
text field) What am I missing ? Can someone please enlighten me.
Thanks Kindly
 
T

Tom Lake

How do I eliminate #Error when using Left Function where some fields are
null ?
Right now I'm using =Left$([FuelCapacity],4) which works fine on the
fields
that are not null, but I get #Error on the null fields. I've tried using
=nz(Left$([FuelCapacity],4)) but still get the #Error. (FuelCapacity is a
text field) What am I missing ? Can someone please enlighten me.
Thanks Kindly
Click to expand...

IIf(IsNull([FuelCapacity]), "", Left([FuelCapacity], 4))

Tom Lake
 
F

fredg

How do I eliminate #Error when using Left Function where some fields are null ?
Right now I'm using =Left$([FuelCapacity],4) which works fine on the fields
that are not null, but I get #Error on the null fields. I've tried using
=nz(Left$([FuelCapacity],4)) but still get the #Error. (FuelCapacity is a
text field) What am I missing ? Can someone please enlighten me.
Thanks Kindly
Click to expand...

1) You don't need Left$(). You can use Left()

=Left([FuelCapacity],4)

Or..

2) it you want Left$, you can use:
=IIf(Not IsNull([FuelCapacity]),Left$([FuelCapacity],4),"")
OR..

3) =Left$(Nz([FuelCapacity]),4)
 
M

Marshall Barton

Gabby said:
How do I eliminate #Error when using Left Function where some fields are null ?
Right now I'm using =Left$([FuelCapacity],4) which works fine on the fields
that are not null, but I get #Error on the null fields. I've tried using
=nz(Left$([FuelCapacity],4)) but still get the #Error. (FuelCapacity is a
text field) What am I missing ? Can someone please enlighten me.
Click to expand...


If you want a Null result when FuelCapacity is Null:

=Left([FuelCapacity],4)

If you want a ZLS:

=Left$(Nz([FuelCapacity], ""), 4)
 
J

John Spencer

Simplest fix is to use LEFT and not LEFT$

Left$(Null) generates an error
Left(Null) return Null

In other words, Left$ requires the argument is a string or that the argument
can be converted to a string. Null cannot be converted.
 
G

Gabby Girl

Thanks to all of you. Your help is always greatly appreciated.
Have a great day.


John Spencer said:
Simplest fix is to use LEFT and not LEFT$

Left$(Null) generates an error
Left(Null) return Null

In other words, Left$ requires the argument is a string or that the argument
can be converted to a string. Null cannot be converted.

Gabby Girl said:
How do I eliminate #Error when using Left Function where some fields are
null ?
Right now I'm using =Left$([FuelCapacity],4) which works fine on the
fields
that are not null, but I get #Error on the null fields. I've tried using
=nz(Left$([FuelCapacity],4)) but still get the #Error. (FuelCapacity is a
text field) What am I missing ? Can someone please enlighten me.
Thanks Kindly
Click to expand...
Click to expand...
 
Top