D
david epsom dot com dot au
Technically, infinity is "Not A Number" (abbreviated NAN in most
discussions). Access will only recognise two kinds of NAN's in numeric
fields: ERROR and NULL. ERROR can't be used for anything in Access, because
in Access it propagates through all expressions, including any expression
you try to use to test for that value. You can use NULL as a value to
represent any kind of NAN. If you have to represent more than one kind of
NAN (infinity, unknown,divide by zero, underflow, overflow,not applicable,
etc), then you have to either (a) use another field to indicate which value
is meant or (b) code both kinds of information (Numeric and NAN) into one
field using your own coding scheme.
Coding two different kinds of information into one field is almost always a
bad idea. The only reason anyone does it is because it is often so much
easier and more convenient than properly separating out the different kinds
of information into separate fields. In this case, it is a toss up as to if
coding magic numbers to represent symbolic states (for example -1 =
infinity, or <0 = infinity) is going to be easier for you to handle than
using NULL.
Using NULL to represent infinity, your forms and reports will show a blank
value for the Limit. Also, you will be able to do simple arithmetic on the
NULL value. For example: How many remaining uses does a division have?
Div_A: 10 -6 = 4;
Div_B: Null - 32 = Null
Average remaining uses: Null
So your forms and reports will automatically show derived values as blank
where appropriate.
Compare use of a numeric value, -1, to represent infinity:
Div_A: 10-6 = 4
Div_B: -1-32=-33
Average remaining uses: -14.5
(david)
used by department within my company. Some departments may use this
equipment on a limited basis (say 100 times). Other departments, however,
can use this equipment as much as they want. I need to enter a value for
the latter group that will work in queries. Basically, how tell Access that
these departments have unlimited use of this equipment?
discussions). Access will only recognise two kinds of NAN's in numeric
fields: ERROR and NULL. ERROR can't be used for anything in Access, because
in Access it propagates through all expressions, including any expression
you try to use to test for that value. You can use NULL as a value to
represent any kind of NAN. If you have to represent more than one kind of
NAN (infinity, unknown,divide by zero, underflow, overflow,not applicable,
etc), then you have to either (a) use another field to indicate which value
is meant or (b) code both kinds of information (Numeric and NAN) into one
field using your own coding scheme.
Coding two different kinds of information into one field is almost always a
bad idea. The only reason anyone does it is because it is often so much
easier and more convenient than properly separating out the different kinds
of information into separate fields. In this case, it is a toss up as to if
coding magic numbers to represent symbolic states (for example -1 =
infinity, or <0 = infinity) is going to be easier for you to handle than
using NULL.
Using NULL to represent infinity, your forms and reports will show a blank
value for the Limit. Also, you will be able to do simple arithmetic on the
NULL value. For example: How many remaining uses does a division have?
Div_A: 10 -6 = 4;
Div_B: Null - 32 = Null
Average remaining uses: Null
So your forms and reports will automatically show derived values as blank
where appropriate.
Compare use of a numeric value, -1, to represent infinity:
Div_A: 10-6 = 4
Div_B: -1-32=-33
Average remaining uses: -14.5
(david)
example, I have data regarding how often a certain type of equipment can beNeelam said:
used by department within my company. Some departments may use this
equipment on a limited basis (say 100 times). Other departments, however,
can use this equipment as much as they want. I need to enter a value for
the latter group that will work in queries. Basically, how tell Access that
these departments have unlimited use of this equipment?