473,321 Members | 1,916 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

IsNull() - I can't get it to work properly..

I'm trying to test a recordset to see if the SQL query is returning a
Null value. However, the IsNull() test always returns a 'False'
response. I even tried:

Dim NullCheck As String
NullCheck = ""
NullCheck = IsNull(NullCheck)

When I halted the code half way through, she was still registering
"False"..... I figure it's something basic, but its got me beat.

Cheers

Reg

Nov 13 '05 #1
10 1724
Null is not the same thing as a zero-length string.

For an explanation, see:
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.

"Regnab" <p.*******@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
I'm trying to test a recordset to see if the SQL query is returning a
Null value. However, the IsNull() test always returns a 'False'
response. I even tried:

Dim NullCheck As String
NullCheck = ""
NullCheck = IsNull(NullCheck)

When I halted the code half way through, she was still registering
"False"..... I figure it's something basic, but its got me beat.

Cheers

Reg

Nov 13 '05 #2
In addition to what Allen's pointed out, String variables can never be Null:
the only variable type than accepts Null values is the Variant.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Regnab" <p.*******@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
I'm trying to test a recordset to see if the SQL query is returning a
Null value. However, the IsNull() test always returns a 'False'
response. I even tried:

Dim NullCheck As String
NullCheck = ""
NullCheck = IsNull(NullCheck)

When I halted the code half way through, she was still registering
"False"..... I figure it's something basic, but its got me beat.

Cheers

Reg

Nov 13 '05 #3
Zero Length Strings and Nulls can be problems.
Note that in SQL land a zero length string is equal to a space (or
whatever SQL chooses to use when given a space).
Consider the queries below:
Table 1 has 9 fields:

UPDATE Table1 SET MyNewField = '';

SELECT *
FROM Table1
WHERE MyNewField = '';
(returns 9 records as expected)

SELECT *
FROM Table1
WHERE MyNewField IS NULL;
(returns zero records as expected)

SELECT *
FROM Table1
WHERE MyNewField = ' ';
(returns 9 records -> NOT what I expect)

SELECT *
FROM Table1
WHERE MyNewField = Space(100);
(returns 9 records -> again, not what I expect)

I expect this seldom causes trouble, but
If we modify a string in a text field with required set to no, and
allow zls to yes, by pressing the BackSpace key until all the
characters are erased, the entry in the field will now be an empty
string, and not a Null, as it would be in a new record. But it and a
record with a Null entry will appear identical.

And consider this query:
SELECT Table1.*, tbl2002Transactions.*
FROM Table1 INNER JOIN tbl2002Transactions ON
tbl2002Transactions.fldDescription LIKE '*' & Table1.MyNewField & '*';

What's your guess? Will it return the records for Table 1 where
MyNewField IS NULL (1), or will it return it return the records for
Table 1 where MyNewField is a zero length string (2), or both (3), or
neither (4). (My guess was wrong).

When I can, I set required to no, and allow zls to no. This allows me
to work with character fields knowing that I will deal only with NULLs
and "REAL" strings. Trouble is I don't know how to do this (allow zls
to no) in MS-SQL, so I end up using a lot of NULLIF functions. I've
pretty well stopped (in any serious app) using bound forms, so I can
control what's put into the fields with my update, append queries; I
try to be VERY careful with Editing Recordsets and Update, UpdateBatch
however,

Nov 13 '05 #4
Lyle, thank you for posting this.

Access Basic (version 1 - 2) used to consider " " to be equal to "".
VBA does not, so it's important to be clear that JET SQL behaves differently
to VBA.

I assume from your post that SQL Server also considers a space equal to a
zero-length string.

I'm with you on setting always AllowZeroLength to No. (Well, there is the
odd exception, such as a required field that is part of a multifield unique
required index.)

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

"lylefair" <ly******@yahoo.ca> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.com...
Zero Length Strings and Nulls can be problems.
Note that in SQL land a zero length string is equal to a space (or
whatever SQL chooses to use when given a space).
Consider the queries below:
Table 1 has 9 fields:

UPDATE Table1 SET MyNewField = '';

SELECT *
FROM Table1
WHERE MyNewField = '';
(returns 9 records as expected)

SELECT *
FROM Table1
WHERE MyNewField IS NULL;
(returns zero records as expected)

SELECT *
FROM Table1
WHERE MyNewField = ' ';
(returns 9 records -> NOT what I expect)

SELECT *
FROM Table1
WHERE MyNewField = Space(100);
(returns 9 records -> again, not what I expect)

I expect this seldom causes trouble, but
If we modify a string in a text field with required set to no, and
allow zls to yes, by pressing the BackSpace key until all the
characters are erased, the entry in the field will now be an empty
string, and not a Null, as it would be in a new record. But it and a
record with a Null entry will appear identical.

And consider this query:
SELECT Table1.*, tbl2002Transactions.*
FROM Table1 INNER JOIN tbl2002Transactions ON
tbl2002Transactions.fldDescription LIKE '*' & Table1.MyNewField & '*';

What's your guess? Will it return the records for Table 1 where
MyNewField IS NULL (1), or will it return it return the records for
Table 1 where MyNewField is a zero length string (2), or both (3), or
neither (4). (My guess was wrong).

When I can, I set required to no, and allow zls to no. This allows me
to work with character fields knowing that I will deal only with NULLs
and "REAL" strings. Trouble is I don't know how to do this (allow zls
to no) in MS-SQL, so I end up using a lot of NULLIF functions. I've
pretty well stopped (in any serious app) using bound forms, so I can
control what's put into the fields with my update, append queries; I
try to be VERY careful with Editing Recordsets and Update, UpdateBatch
however,

Nov 13 '05 #5
Allen Browne wrote:
I'm with you on setting always AllowZeroLength to No. (Well, there is the
odd exception, such as a required field that is part of a multifield unique
required index.)


Hi Allen,

This seems to touch on a cross over post from a couple of years ago from
the cd.theory guys, Bob Badour, in particular, who made an excellent
(IMO) case designing one's data structure such that a field is never
populated with a null value. Their arguments made perfect sense to me
and since, I've tried to follow their lead in my Oracle and Jet projects
(except I can't figure out how to populate a date field with anything
but a legitimate date, so these I do allow nulls).

The above sounds as if you subscribe to this theory? If I'm right, what
do you do with text fields/columns which can legitimately have no values
(optional comments, for example) that may or may not be indexed? For
example, a status field which is populated with "canned" comments from
another table or is left empty. In my own case, I've been setting
Required = Yes and Allow Zero Length to Yes so that I can populate it
with an empty string. That way, searches don't have to trip over nulls
in an indexed field (one of the principle arguments of the theory
fellows I referred to above).

Thanks for any comment from anyone on this.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #6
No, Tim. I don't subscribe to that at all.

IMHO, Null is a very important concept in database theory and practice. The
idea that two unknowns are not treated as the same and not treated as
different (i.e. any comparision yields unknown) makes perfect sense to me,
and that is precisely how null propagation works. I would be completely lost
without my nulls.

Therefore I permit nulls and disallow zero-length-strings in every table.
And I find this important enough that I willl programmatically check the
database to ensure the Allow Zero Length property of every text field is set
to No as part of the quality control before the database is allowed out the
door. (Unfortunately this is necessary since A2000 and later are
inconsistent with the behavior of previous versions, and even inconsistent
within itself, depending how the field was created--interface, DAO, ADO,
DDL.)

As a simple example, code that checks for duplicate clients based on name
and address really needs to consider the person to be a possible duplicate
if the address is null. A string comparison fails for this and many other
simple database requirements.

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

"Tim Marshall" <TI****@PurplePandaChasers.Moertherium> wrote in message
news:dh**********@coranto.ucs.mun.ca...
Allen Browne wrote:
I'm with you on setting always AllowZeroLength to No. (Well, there is the
odd exception, such as a required field that is part of a multifield
unique required index.)


Hi Allen,

This seems to touch on a cross over post from a couple of years ago from
the cd.theory guys, Bob Badour, in particular, who made an excellent (IMO)
case designing one's data structure such that a field is never populated
with a null value. Their arguments made perfect sense to me and since,
I've tried to follow their lead in my Oracle and Jet projects (except I
can't figure out how to populate a date field with anything but a
legitimate date, so these I do allow nulls).

The above sounds as if you subscribe to this theory? If I'm right, what
do you do with text fields/columns which can legitimately have no values
(optional comments, for example) that may or may not be indexed? For
example, a status field which is populated with "canned" comments from
another table or is left empty. In my own case, I've been setting
Required = Yes and Allow Zero Length to Yes so that I can populate it with
an empty string. That way, searches don't have to trip over nulls in an
indexed field (one of the principle arguments of the theory fellows I
referred to above).

Thanks for any comment from anyone on this.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

Nov 13 '05 #7
Lyle, a bit more checking, and this looks like a special case of JET
ignoring any trailing spaces, e.g. it considers "Jones " to be equal to
"Jones".

So I guess we could work around that similar to the approach for
case-sensitive comparisons, e.g.:
SELECT Table1.*
FROM Table1 INNER JOIN Table2
ON StrComp(Table1.Surname, Table2.Surname, 1) = 0;

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

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...
Lyle, thank you for posting this.

Access Basic (version 1 - 2) used to consider " " to be equal to "".
VBA does not, so it's important to be clear that JET SQL behaves
differently to VBA.

I assume from your post that SQL Server also considers a space equal to a
zero-length string.

I'm with you on setting always AllowZeroLength to No. (Well, there is the
odd exception, such as a required field that is part of a multifield
unique required index.)

"lylefair" <ly******@yahoo.ca> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.com...
Zero Length Strings and Nulls can be problems.
Note that in SQL land a zero length string is equal to a space (or
whatever SQL chooses to use when given a space).
Consider the queries below:
Table 1 has 9 fields:

UPDATE Table1 SET MyNewField = '';

SELECT *
FROM Table1
WHERE MyNewField = '';
(returns 9 records as expected)

SELECT *
FROM Table1
WHERE MyNewField IS NULL;
(returns zero records as expected)

SELECT *
FROM Table1
WHERE MyNewField = ' ';
(returns 9 records -> NOT what I expect)

SELECT *
FROM Table1
WHERE MyNewField = Space(100);
(returns 9 records -> again, not what I expect)

I expect this seldom causes trouble, but
If we modify a string in a text field with required set to no, and
allow zls to yes, by pressing the BackSpace key until all the
characters are erased, the entry in the field will now be an empty
string, and not a Null, as it would be in a new record. But it and a
record with a Null entry will appear identical.

And consider this query:
SELECT Table1.*, tbl2002Transactions.*
FROM Table1 INNER JOIN tbl2002Transactions ON
tbl2002Transactions.fldDescription LIKE '*' & Table1.MyNewField & '*';

What's your guess? Will it return the records for Table 1 where
MyNewField IS NULL (1), or will it return it return the records for
Table 1 where MyNewField is a zero length string (2), or both (3), or
neither (4). (My guess was wrong).

When I can, I set required to no, and allow zls to no. This allows me
to work with character fields knowing that I will deal only with NULLs
and "REAL" strings. Trouble is I don't know how to do this (allow zls
to no) in MS-SQL, so I end up using a lot of NULLIF functions. I've
pretty well stopped (in any serious app) using bound forms, so I can
control what's put into the fields with my update, append queries; I
try to be VERY careful with Editing Recordsets and Update, UpdateBatch
however,

Nov 13 '05 #8
Bri

lylefair wrote:
Zero Length Strings and Nulls can be problems.
Note that in SQL land a zero length string is equal to a space (or
whatever SQL chooses to use when given a space). <snip> When I can, I set required to no, and allow zls to no. This allows me
to work with character fields knowing that I will deal only with NULLs
and "REAL" strings. Trouble is I don't know how to do this (allow zls
to no) in MS-SQL, so I end up using a lot of NULLIF functions. I've
pretty well stopped (in any serious app) using bound forms, so I can
control what's put into the fields with my update, append queries; I
try to be VERY careful with Editing Recordsets and Update, UpdateBatch
however,


Lyle,

I can't remember who I got this suggestion from (someone in this
newsgroup for sure), but I have taken to testing the length of the
string instead of for Null.
egs (assumed Linked SQL Server Tables)
If Len(stString & "") = 0 Then
If Len(Me!Text1 & "") = 0 Then
If Len(rs!Text1 & "") = 0 Then
Select * From Table Where Len(Field & "") = 0

If SQL Server actually returns a space under ZLS Required scenario you
describe, then it will screw up even this.

Damn, just when I thought I had something that would work in all
scenarios, you find the exception.

Thanks for the heads up.

--
Bri

Nov 13 '05 #9
Wow - thanks for the responses. Just wondering how this translates to
recordsets - when I try the NZ() or Len() to check if its a 0 length
value returned, it returns an error "No Current Record". What operator
can I use to check if no records where returned?? I'm trying to get it
to check in one table for a User ID, and if it's not there, look in
another table. Thanks again for all your help.

Cheers

Reg

Nov 13 '05 #10
Regnab wrote in message
<11**********************@g47g2000cwa.googlegroups .com> :
Wow - thanks for the responses. Just wondering how this translates to
recordsets - when I try the NZ() or Len() to check if its a 0 length
value returned, it returns an error "No Current Record". What operator
can I use to check if no records where returned?? I'm trying to get it
to check in one table for a User ID, and if it's not there, look in
another table. Thanks again for all your help.

Cheers

Reg


Now, this is perhaps something else - whether a recordset contains
records or not. I'd try investigating the .RecordCount property for a
DAO recordset, or test for .Bof/.Eof on an ADO recordset i e

If (rsDAO.RecordCount > 0) Then
' contains records
end if

if ((not rsADO.Bof) and (not rsADO.Eof)) Then
' contains records
end if

--
Roy-Vidar

Nov 13 '05 #11

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Trev | last post by:
I have two tables, tblMTO and tblIMPORT_MTO. If I import an entire MTO into the import table I want to create a delta from it (i.e. leave only the changed items). I have a view (simplified) ...
6
by: Martin | last post by:
Dear Group Just wondered how I can avoid the CHAR(32) to be inserted if @String1 is NULL? SET @String3 = ISNULL(@String1,'') + CHAR(32) + ISNULL(@String2,'') Thanks very much for your...
8
by: NickName | last post by:
Hi, I seemed to me IsNull Evaluation within EXEC fails. Here's some more detail of the problem. -- goal: provide one parameter (of various value) to generate a -- report declare @col4...
1
by: Brent | last post by:
I'm trying to keep the logic of my web pages to a minimum. One of the problems I face regularly is dealing with DBNull's. I know how to code my way out of them, but I thought perhaps it'd be easier...
4
by: Paul Spratley | last post by:
Hi all Firstly this my first time posting to technical groups - so any mistakes I apologise for in advance. I am trying to count records in several secondary tables for the same run in a...
2
by: Raoul Watson | last post by:
I have used isNull statement for as long as I have used VB.. Recently I am devugging a program and it is very clear that the "IsNull" function sometimes would return a true even when the value is...
6
by: =?iso-8859-2?Q?Marcin_Dzi=F3bek?= | last post by:
Hi All: I need to get (filter in) some dataview's rows with DBNULLs in column of boolean type: Actually to get the only rows with DBNULL, I use code like this: DV.RowFilter =...
22
by: PW | last post by:
Hi All, I go into debug mode with the code below and varReturnVal is Null, but the code still is run. Any idea why? Dim varReturnVal As Variant Dim intDogID As Integer
8
by: DBlearner | last post by:
Good afternoon folks, I'm a total novice at this game. I'm a student at an University in DC and is taking a basic course in Access and database. I need some help writing a nesting expression...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.