How to preserve null value in a access calculated field using iif

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have a field in sql server table named hasmajor. Now this field is of type
smallint. The front end is access connected to sql server via odbc. Now the
field value in Access for this field is:

1
0
0
1

0
1

I am using a function =IIf([HasMajor],"Y","N") as the control source for
another column that will display the above field value as Y N.

I am expecting the values of the above field in the calculated column as the
following:

Y
N
N
Y

N
Y
This means I am expecting neither N or Y when there is null value. However
the null value is being displayed as N. I have two null values which in the
calculated column is N. How to keep it null in the calucated column?
I would appreciate any help in advance. Thanks.
 
Thanks Roger and Ofer,
I appreciate it very much. Regards

Ofer Cohen said:
Try nested IIf

= IIf([HasMajor] Is Null,Null,IIf([HasMajor],"Y","N"))

--
Good Luck
BS"D


Jack said:
Hi,
I have a field in sql server table named hasmajor. Now this field is of type
smallint. The front end is access connected to sql server via odbc. Now the
field value in Access for this field is:

1
0
0
1

0
1

I am using a function =IIf([HasMajor],"Y","N") as the control source for
another column that will display the above field value as Y N.

I am expecting the values of the above field in the calculated column as the
following:

Y
N
N
Y

N
Y
This means I am expecting neither N or Y when there is null value. However
the null value is being displayed as N. I have two null values which in the
calculated column is N. How to keep it null in the calucated column?
I would appreciate any help in advance. Thanks.
Click to expand...
Click to expand...
 
Back
Top