if null or zero

[Guest]

Simple but I can't get it:
if [fielda]is null or equal to zero, then [fieldb], otherwise [fielda]

 

[Gina Whipp]

SierraLightFoot,

Is the below what you are looking for?

If(IsNull([fielda]) Or [fielda]=0,[fieldb],[fielda])

OR in a query

NameOfField: IIf(IsNull([fielda]) Or [fielda]=0,[fieldb],[fielda])

HTH,
Gina Whipp

 

[Guest]

I am using this expresion: NameOfField: IIf(IsNull([fielda]) Or
[fielda]=0,[fieldb],[fielda])
but where field is =0 I get "error' as return.

SierraLightFoot,

Is the below what you are looking for?

If(IsNull([fielda]) Or [fielda]=0,[fieldb],[fielda])

OR in a query

NameOfField: IIf(IsNull([fielda]) Or [fielda]=0,[fieldb],[fielda])

HTH,
Gina Whipp

Simple but I can't get it:
if [fielda]is null or equal to zero, then [fieldb], otherwise [fielda]

 

[Gina Whipp]

Is it a numeric field, is there a '0' in the field or is it empty (Null)? I
was going off of what you asked 'equal to zero' but if it's not a numeric
field and the field contains no zeroes then try: NameOfField:
IIf(IsNull([fielda]), [fieldb],[fielda])

I am using this expresion: NameOfField: IIf(IsNull([fielda]) Or
[fielda]=0,[fieldb],[fielda])
but where field is =0 I get "error' as return.

SierraLightFoot,

Is the below what you are looking for?

If(IsNull([fielda]) Or [fielda]=0,[fieldb],[fielda])

OR in a query

NameOfField: IIf(IsNull([fielda]) Or [fielda]=0,[fieldb],[fielda])

HTH,
Gina Whipp

Simple but I can't get it:
if [fielda]is null or equal to zero, then [fieldb], otherwise [fielda]

 

[Guest]

Looks right, but what is the differance in if vs iif

 

[Guest]

Looks right, but what is the differance in if vs iif ?
thanks

Looks right, but what is the differance in if vs iif

Simple but I can't get it:
if [fielda]is null or equal to zero, then [fieldb], otherwise [fielda]

 

[Gina Whipp]

SierraLightFoot,

Going to need one of those MVP's to explain that... All I can tell you that
'IIf' evaluates (Expression, TruePart, FalsePart) and will only return True
or False. 'If' allows conditional excecution, based on the evaluation of
the expression (such as, if... then... else).

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

Looks right, but what is the differance in if vs iif ?
thanks

Looks right, but what is the differance in if vs iif

Simple but I can't get it:
if [fielda]is null or equal to zero, then [fieldb], otherwise [fielda]

 

[David F Cox]

While we wait for an MVP, IIF (arg1, arg2, arg3) is an inbuilt function
which returns a variant value.

"If" is a language element.

SierraLightFoot,

Going to need one of those MVP's to explain that... All I can tell you
that 'IIf' evaluates (Expression, TruePart, FalsePart) and will only
return True or False. 'If' allows conditional excecution, based on the
evaluation of the expression (such as, if... then... else).

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

Looks right, but what is the differance in if vs iif ?
thanks

Looks right, but what is the differance in if vs iif

:

Simple but I can't get it:
if [fielda]is null or equal to zero, then [fieldb], otherwise [fielda]

 

[Marshall Barton]

SierraLightFoot,

Going to need one of those MVP's to explain that... All I can tell you that
'IIf' evaluates (Expression, TruePart, FalsePart) and will only return True
or False. 'If' allows conditional excecution, based on the evaluation of
the expression (such as, if... then... else).

In addition to that, the key difference is that IIf is a
**function** that only makes sense when used in an
expression. Expressions can be used in many contexts
(control source expression, calculated query fields, VBA
expressions, etc).

If is a **statement** in the VBA language that affects which
following statements are executed. It can not be used
anywhere else.

 

[Gina Whipp]

Oh dear, too late to rephrase.... Big THANKS David...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

While we wait for an MVP, IIF (arg1, arg2, arg3) is an inbuilt function
which returns a variant value.

"If" is a language element.

SierraLightFoot,

Going to need one of those MVP's to explain that... All I can tell you
that 'IIf' evaluates (Expression, TruePart, FalsePart) and will only
return True or False. 'If' allows conditional excecution, based on the
evaluation of the expression (such as, if... then... else).

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II

Looks right, but what is the differance in if vs iif ?
thanks

:

Looks right, but what is the differance in if vs iif

:

Simple but I can't get it:
if [fielda]is null or equal to zero, then [fieldb], otherwise
[fielda]