'= null' vs 'is null' in access2007 query
in [Help]
|
Prev: upgrading access2003 to access2007
Next: where do I find the UsysRibbon table to add a custom ribbon
From: Roger on 29 Jul 2010 19:55 in access97, this works SELECT businessId FROM tblBusiness WHERE baanPostalId = null but not in access2007, it needs to be SELECT businessId FROM tblBusiness WHERE baanPostalId is null is there a compatiblity option that allows the original version to run ?
From: Bob Barrows on 29 Jul 2010 20:50 Roger wrote: > in access97, this works > SELECT businessId > FROM tblBusiness > WHERE baanPostalId = null > > > but not in access2007, it needs to be > SELECT businessId > FROM tblBusiness > WHERE baanPostalId is null > > is there a compatiblity option that allows the original version to > run ? Given that "baanPostalId = null: should not have "worked" in A97, I doubt you will get a workaround. It likely ran without error, but it almost certainly did not return the results you wanted. If it did, it is likely that baanPostalId did not contain what you thought it did. Nothing ever "equals" Null, not even in A95, let alone A97. That's part of the definition of Null: "unknown value" never equals "unknown value". Only a known value can equal a known value. Unknown can _be_ unknown, but it can never equal it. Are you telling me that attempting to use "baanPostalId = null" throws an error in A2007? If so, all I can say is "Bravo!".
From: Allen Browne on 29 Jul 2010 21:03
To add to what Bob said, the idea that Unknown = Unknown is not valid. It's error #5 in this list: Common Errors with Null at: http://allenbrowne.com/casu-12.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Bob Barrows" <reb01501(a)yahoo.com> wrote in message news:Ckp4o.50569$0A5.33692(a)newsfe22.iad... > Roger wrote: >> in access97, this works >> SELECT businessId >> FROM tblBusiness >> WHERE baanPostalId = null >> >> >> but not in access2007, it needs to be >> SELECT businessId >> FROM tblBusiness >> WHERE baanPostalId is null >> >> is there a compatiblity option that allows the original version to >> run ? > > Given that "baanPostalId = null: should not have "worked" in A97, I doubt > you will get a workaround. It likely ran without error, but it almost > certainly did not return the results you wanted. If it did, it is likely > that baanPostalId did not contain what you thought it did. > > Nothing ever "equals" Null, not even in A95, let alone A97. That's part of > the definition of Null: "unknown value" never equals "unknown value". Only > a known value can equal a known value. Unknown can _be_ unknown, but it > can never equal it. > > Are you telling me that attempting to use "baanPostalId = null" throws an > error in A2007? If so, all I can say is "Bravo!". > |