currency data type

[Mark Carlyle via AccessMonster.com]

I have a statement that says

if [field]=null then [field]=0

to change the field from a possible null to a 0

the field is a type currency and when it runs it will not change it to a 0

I have calculations that needs to work off the value of the field and they
will not work since it is a null. I could change the info later but it is in
a query and do not know how to do that later change.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200507/1

[Lye Fairfield]

"Mark Carlyle via AccessMonster.com" <fo***@AccessMonster.com> wrote in
news:51***********@AccessMonster.com:

I have a statement that says

if [field]=null then [field]=0

to change the field from a possible null to a 0

the field is a type currency and when it runs it will not change it to
a 0

I have calculations that needs to work off the value of the field and
they will not work since it is a null. I could change the info later
but it is in a query and do not know how to do that later change.

No field is a Null. Null means unknown, not given. It is not a value.

Null is not equal to Null. Nothing is equal to Null. Generally we test for
Null with the assertion Is Null.

We should not use zero for null. Zero is not Null. Mathemtical calculations
which use zero for Null are often flawed.

GUIs which accept default Null in place of default zero lack rigor.

***

But

In your calculations you could use:

Nz(Field, 0)

You could look up Nz in Access/VBA help to learn about it.

[Lye Fairfield]

"Mark Carlyle via AccessMonster.com" <fo***@AccessMonster.com> wrote in
news:51***********@AccessMonster.com:

I have a statement that says

if [field]=null then [field]=0

to change the field from a possible null to a 0

the field is a type currency and when it runs it will not change it to
a 0

I have calculations that needs to work off the value of the field and
they will not work since it is a null. I could change the info later
but it is in a query and do not know how to do that later change.

No field is a Null. Null means unknown, not given. It is not a value.

Null is not equal to Null. Nothing is equal to Null. Generally we test for
Null with the assertion Is Null.

We should not use zero for null. Zero is not Null. Mathemtical calculations
which use zero for Null are often flawed.

GUIs which accept default Null in place of default zero lack rigor.

***

But

In your calculations you could use:

Nz(Field, 0)

You could look up Nz in Access/VBA help to learn about it.

[rude person]

On Fri, 15 Jul 2005 17:34:25 GMT, "Mark Carlyle via AccessMonster.com"
<fo***@AccessMonster.com> wrote:

I have a statement that says

if [field]=null then [field]=0

to change the field from a possible null to a 0

the field is a type currency and when it runs it will not change it to a 0

I have calculations that needs to work off the value of the field and they
will not work since it is a null. I could change the info later but it is in
a query and do not know how to do that later change.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200507/1

Look up the function Nz in help. In particular Nz(x,0) is zero if x is
null and equals x otherwise (not sure about types in the latter case).

In spite of what Lyle recommends Access treats null as a value, with
the special properties that comparing it with anything gives the
answer false, and with the special function isnull() and (in SQL) IS
NULL.

Ideally null wouldn't exist (!) but it cannot be avoided in outer
joins, for example.

[rude person]

On Fri, 15 Jul 2005 17:34:25 GMT, "Mark Carlyle via AccessMonster.com"
<fo***@AccessMonster.com> wrote:

I have a statement that says

if [field]=null then [field]=0

to change the field from a possible null to a 0

the field is a type currency and when it runs it will not change it to a 0

I have calculations that needs to work off the value of the field and they
will not work since it is a null. I could change the info later but it is in
a query and do not know how to do that later change.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200507/1

Look up the function Nz in help. In particular Nz(x,0) is zero if x is
null and equals x otherwise (not sure about types in the latter case).

In spite of what Lyle recommends Access treats null as a value, with
the special properties that comparing it with anything gives the
answer false, and with the special function isnull() and (in SQL) IS
NULL.

Ideally null wouldn't exist (!) but it cannot be avoided in outer
joins, for example.

[Trevor Best]

rude person wrote:

In spite of what Lyle recommends Access treats null as a value, with
the special properties that comparing it with anything gives the
answer false
Actually it gives the answer null, not false. It may look false because
it fails any condition by not being true, e.g.

if a = null then
' this bit will never execute
endif

if a <> null then
' neither will this
endif
Ideally null wouldn't exist (!) but it cannot be avoided in outer
joins, for example.

It doesn't exist as such, but must exist in order to at least satisfy
Codd's 3rd rule

--
[OO=00=OO]

[Trevor Best]

rude person wrote:

In spite of what Lyle recommends Access treats null as a value, with
the special properties that comparing it with anything gives the
answer false
Actually it gives the answer null, not false. It may look false because
it fails any condition by not being true, e.g.

if a = null then
' this bit will never execute
endif

if a <> null then
' neither will this
endif
Ideally null wouldn't exist (!) but it cannot be avoided in outer
joins, for example.

It doesn't exist as such, but must exist in order to at least satisfy
Codd's 3rd rule

--
[OO=00=OO]

[lylefair]

In Access 2003 (tested only on my laptop) operations with nulls
generate run-time errors. Yay! Who said there was nothing new or better
in Access 2003?

[lylefair]

In Access 2003 (tested only on my laptop) operations with nulls
generate run-time errors. Yay! Who said there was nothing new or better
in Access 2003?

[rude person]

On Sat, 16 Jul 2005 15:01:39 +0100, Trevor Best <no****@besty.org.uk>
wrote:

rude person wrote:

In spite of what Lyle recommends Access treats null as a value, with
the special properties that comparing it with anything gives the
answer false

Actually it gives the answer null, not false. It may look false because
it fails any condition by not being true, e.g.

if a = null then
' this bit will never execute
endif

if a <> null then
' neither will this
endif

True. Or perhaps I should say "Correct"

By treating null like a value I meant that you can assign it and set
it as a default value. In fact you can even include it in a select
list (in JET and Oracle for example). These are cases where you have
something in your hand and decide to set its value to null, rather
than null occuring "naturally" as it were.

Ideally null wouldn't exist (!) but it cannot be avoided in outer
joins, for example.

It doesn't exist as such, but must exist in order to at least satisfy
Codd's 3rd rule

[rude person]

On Sat, 16 Jul 2005 15:01:39 +0100, Trevor Best <no****@besty.org.uk>
wrote:

rude person wrote:

In spite of what Lyle recommends Access treats null as a value, with
the special properties that comparing it with anything gives the
answer false

Actually it gives the answer null, not false. It may look false because
it fails any condition by not being true, e.g.

if a = null then
' this bit will never execute
endif

if a <> null then
' neither will this
endif

True. Or perhaps I should say "Correct"

By treating null like a value I meant that you can assign it and set
it as a default value. In fact you can even include it in a select
list (in JET and Oracle for example). These are cases where you have
something in your hand and decide to set its value to null, rather
than null occuring "naturally" as it were.

Ideally null wouldn't exist (!) but it cannot be avoided in outer
joins, for example.

It doesn't exist as such, but must exist in order to at least satisfy
Codd's 3rd rule