Null values and integer fields

[Nathan Sokalski]

I have several fields that will sometimes be null, and sometimes be
integers. But Access refuses to let me use NULL as a value in an integer
field. Is it possible to make an integer field null? I do not want to need
to add an extra field for each of these fields to determine whether or not
they have a value. Any help would be appreciated. Thanks.

 

[KARL DEWEY]

Do you need the null for processing data or for display? If for display why
not use 0 (zero) and then an IIF statement for display?
IIF([YourField] = 0, NULL, [YourField])
or
IIF([YourField] = 0, "", [YourField])

 

[John Spencer]

A number field with size Integer can store null values.

A variable dimmed as Integer can NOT have null assigned to it.

You need to post a little more detail on what you are attempting.

Oh, an integer field could have its required property set to YES (True) and
then you could not store Null in the field.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Olduke]

I have several fields that will sometimes be null, and sometimes be
integers. But Access refuses to let me use NULL as a value in an integer
field. Is it possible to make an integer field null? I do not want to need
to add an extra field for each of these fields to determine whether or not
they have a value. Any help would be appreciated. Thanks.

Karl is correct. The default value for a field set to Number-Integer is 0
(zero)
The field can not be null. You would search for a zero to perform your task.

 

[Nathan Sokalski]

It is for a calculation, so setting it to 0 would not work, because 0 is one
of the possible values that might be used in the calculation. The same is
true for all other integers, so the only value left to test for is NULL, but
if NULL cannot be stored in an integer field then I am stuck with using an
extra field.
--
Nathan Sokalski
(e-mail address removed)
http://www.nathansokalski.com/

Do you need the null for processing data or for display? If for display
why
not use 0 (zero) and then an IIF statement for display?
IIF([YourField] = 0, NULL, [YourField])
or
IIF([YourField] = 0, "", [YourField])

--
KARL DEWEY
Build a little - Test a little

I have several fields that will sometimes be null, and sometimes be
integers. But Access refuses to let me use NULL as a value in an integer
field. Is it possible to make an integer field null? I do not want to
need
to add an extra field for each of these fields to determine whether or
not
they have a value. Any help would be appreciated. Thanks.

 

[KARL DEWEY]

As John said you can have a null in an integer field if there is no default
and not a required field.
--
KARL DEWEY
Build a little - Test a little

It is for a calculation, so setting it to 0 would not work, because 0 is one
of the possible values that might be used in the calculation. The same is
true for all other integers, so the only value left to test for is NULL, but
if NULL cannot be stored in an integer field then I am stuck with using an
extra field.
--
Nathan Sokalski
(e-mail address removed)
http://www.nathansokalski.com/

Do you need the null for processing data or for display? If for display
why
not use 0 (zero) and then an IIF statement for display?
IIF([YourField] = 0, NULL, [YourField])
or
IIF([YourField] = 0, "", [YourField])

--
KARL DEWEY
Build a little - Test a little

I have several fields that will sometimes be null, and sometimes be
integers. But Access refuses to let me use NULL as a value in an integer
field. Is it possible to make an integer field null? I do not want to
need
to add an extra field for each of these fields to determine whether or
not
they have a value. Any help would be appreciated. Thanks.

 

[John Spencer]

I am sorry, but I strongly disagree.

A default value of zero, does not prevent one from setting the value of a
field to null. A field that has its required property set to yes, means
that you cannot store null in the field.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..