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 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
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
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,
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,
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
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
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,
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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)
...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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 =...
|
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
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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...
| |