Does access support NaN's?

[Guest]

If so how do you insert one via a TSQL command? Is there a special define?

Occasionally I'd like to insert NaN's into various double fields from a TSQL
statement.

Thanks

 

[Douglas J. Steele]

You can't use TSQL with Access, so it's sort of an academic question.

How does NaN differ from Null?

 

[Tom Ellison]

Dear Doug:

The difference is profound, but subtle.

NaN is "not a number."

NULL is "not a value."

NaN says, "this is supposed to be a number, but there isn't any."

NULL says, "this is supposed to be a value, but there isn't any."

So, how many angels can dance on the head of a pin? NaN is a proper answer,
but NULL is too general.

Tom Ellison

 

[Douglas J. Steele]

But is a Null value in a numeric field the same as a Null value in a text
field? <g>

 

[Tom Ellison]

Dear Doug:

Certainly not! A null value in a numeric column means there is no number,
but a null value in a text column means there is no text. Remember: yes,
we have no bananas! That's not the same as "she has no name." Well,
actually, it is "we don't know if we have any bananas" but try to make a big
hit song out of that!

Tom Ellison

 

[david epsom dot com dot au]

If you manage to get a NAN into a field, you will be
able to see it in Access, but you can't really do
anything with or about it. It's handled more or less
the same way an 'error' value is handled: you can't
test for it or capture it or trace it or anything.

To get a NAN value in Access, you need to generate
it outside Access. A DLL call is the obvious way.

I don't know of any way to generate a NAN in Access.
If you divide by zero or take the log of zero, VB
generates an exception, and the target variable is
not updated.

It may be that TSQL is the same: you may be able to
insert a NAN into a recordset by calling a DLL.


(david)

 

[Tony Toews]

NaN says, "this is supposed to be a number, but there isn't any."

But, I use -1 for that or sometimes -2 or even -9999. Doesn't
everyone?

Tony <just kidding>
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tom Ellison]

Dear Tony:

Sure! It works so well in aggregates that way!

Tom Ellison

 

[david epsom dot com dot au]

Null is as general as you define it to be. Many people think
that the distinction between Null and an empty string is not
useful. Certainly, if your database is connected only to a
Web interface, the distinction between Null and an empty string
is not normally useful.

Most of the time, the distinction between the different NANs
is not import to me. Most of the time, the distinction between
the different NANs and an empty string is not important to me

My opinion :~) is that the difference between NAN and Null is
subtle but not profound: a Null is an element of a set of different
kinds of Nulls: a NAN is an element of a subset of that set.


(david)

 

[Tom Ellison]

Dear David:

Seriously now, I think you are mostly correct. The function of NULL is not
lost, however, in a Web interface, or any other interface that does not
display it, in that the NULL still can and should have significant effects
withing the database and query engine.

Tom Ellison

 

[John Vinson]

Dear Doug:

The difference is profound, but subtle.

NaN is "not a number."

NULL is "not a value."

NaN says, "this is supposed to be a number, but there isn't any."

NULL says, "this is supposed to be a value, but there isn't any."

So, how many angels can dance on the head of a pin? NaN is a proper answer,
but NULL is too general.

"The number you have reached is an imaginary number. Please pick up
your telephone, rotate it 90 degrees, and dial again."

- a friend's answering machine message some years back

John W. Vinson[MVP]

 

[Tony Toews]

Sure! It works so well in aggregates that way!

<chuckle> Maybe that's the problem with statistics.


Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tom Ellison]

Phone message on answering maching at math department:

The phone you have reached is a complex number, containing an imaginary
component.

Using two telephone receivers, rotate one 90 degrees to the other and speak
in cartesian coordinates.

Tom Ellison