434,587 Members | 1,101 Online
Need help? Post your question and get tips & solutions from a community of 434,587 IT Pros & Developers. It's quick & easy.

# currency data type

 P: n/a 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 Nov 13 '05 #1
10 Replies

 P: n/a "Mark Carlyle via 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. Nov 13 '05 #2

 P: n/a "Mark Carlyle via 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. Nov 13 '05 #3

 P: n/a On Fri, 15 Jul 2005 17:34:25 GMT, "Mark Carlyle via AccessMonster.com" wrote: I have a statement that saysif [field]=null then [field]=0to change the field from a possible null to a 0the field is a type currency and when it runs it will not change it to a 0I have calculations that needs to work off the value of the field and theywill not work since it is a null. I could change the info later but it is ina query and do not know how to do that later change. --Message posted via AccessMonster.comhttp://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. Nov 13 '05 #4

 P: n/a On Fri, 15 Jul 2005 17:34:25 GMT, "Mark Carlyle via AccessMonster.com" wrote: I have a statement that saysif [field]=null then [field]=0to change the field from a possible null to a 0the field is a type currency and when it runs it will not change it to a 0I have calculations that needs to work off the value of the field and theywill not work since it is a null. I could change the info later but it is ina query and do not know how to do that later change. --Message posted via AccessMonster.comhttp://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. Nov 13 '05 #5

 P: n/a 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] Nov 13 '05 #6

 P: n/a 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] Nov 13 '05 #7

 P: n/a 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? Nov 13 '05 #8

 P: n/a 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? Nov 13 '05 #9

 P: n/a On Sat, 16 Jul 2005 15:01:39 +0100, Trevor Best 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 falseActually it gives the answer null, not false. It may look false becauseit fails any condition by not being true, e.g.if a = null then ' this bit will never executeendifif a <> null then ' neither will thisendif 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 satisfyCodd's 3rd rule Nov 13 '05 #10

 P: n/a On Sat, 16 Jul 2005 15:01:39 +0100, Trevor Best 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 falseActually it gives the answer null, not false. It may look false becauseit fails any condition by not being true, e.g.if a = null then ' this bit will never executeendifif a <> null then ' neither will thisendif 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 satisfyCodd's 3rd rule Nov 13 '05 #11

### This discussion thread is closed

Replies have been disabled for this discussion.