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.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200507/1

[Lye Fairfield]

Re: currency data type

"Mark Carlyle via AccessMonster.c om" <forum@AccessMo nster.com> wrote in
news:515DDA35EE B46@AccessMonst er.com:
[color=blue]
>
> 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.[/color]

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]

Re: currency data type

"Mark Carlyle via AccessMonster.c om" <forum@AccessMo nster.com> wrote in
news:515DDA35EE B46@AccessMonst er.com:
[color=blue]
>
> 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.[/color]

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]

Re: currency data type

On Fri, 15 Jul 2005 17:34:25 GMT, "Mark Carlyle via AccessMonster.c om"
<forum@AccessMo nster.com> wrote:
[color=blue]
>
>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.c om
>http://www.accessmonster.com/Uwe/For...ccess/200507/1[/color]

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]

Re: currency data type

On Fri, 15 Jul 2005 17:34:25 GMT, "Mark Carlyle via AccessMonster.c om"
<forum@AccessMo nster.com> wrote:
[color=blue]
>
>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.c om
>http://www.accessmonster.com/Uwe/For...ccess/200507/1[/color]

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]

Re: currency data type

rude person wrote:
[color=blue]
> 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[/color]

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
[color=blue]
> Ideally null wouldn't exist (!) but it cannot be avoided in outer
> joins, for example.[/color]

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

--
[OO=00=OO]

[Trevor Best]

Re: currency data type

rude person wrote:
[color=blue]
> 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[/color]

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
[color=blue]
> Ideally null wouldn't exist (!) but it cannot be avoided in outer
> joins, for example.[/color]

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

--
[OO=00=OO]

[lylefair@yahoo.ca]

Re: currency data type

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@yahoo.ca]

Re: currency data type

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]

Re: currency data type

On Sat, 16 Jul 2005 15:01:39 +0100, Trevor Best <nospam@besty.o rg.uk>
wrote:
[color=blue]
>rude person wrote:
>[color=green]
>> 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[/color]
>
>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
>[/color]
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.
[color=blue][color=green]
>> Ideally null wouldn't exist (!) but it cannot be avoided in outer
>> joins, for example.[/color]
>
>It doesn't exist as such, but must exist in order to at least satisfy
>Codd's 3rd rule[/color]

[rude person]

Re: currency data type

On Sat, 16 Jul 2005 15:01:39 +0100, Trevor Best <nospam@besty.o rg.uk>
wrote:
[color=blue]
>rude person wrote:
>[color=green]
>> 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[/color]
>
>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
>[/color]
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.
[color=blue][color=green]
>> Ideally null wouldn't exist (!) but it cannot be avoided in outer
>> joins, for example.[/color]
>
>It doesn't exist as such, but must exist in order to at least satisfy
>Codd's 3rd rule[/color]