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

[Jack]

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.

 

[Roger Carlson]

I'd use:

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

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Ofer Cohen]

Try nested IIf

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

 

[Jack]

Thanks Roger and Ofer,
I appreciate it very much. Regards

Try nested IIf

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

--
Good Luck
BS"D

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.