473,225 Members | 1,419 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,225 software developers and data experts.

Puzzling "isNull" failing

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 not Null. Any
wild shot or theory as to why this is happening is appreciated. Also, any
other alternative is appreciated since I am stuck and cannot proceed without
fixing this issue (tried = Null and that doesn't work either).

Basically, I have a field in a databsse which could be null, so in the code
i have:

' mfld(x) would have field names like "NAME" etc.

If IsNull(datPrimaryRS.Recordset(mfld(x))) Then
fvalue = ""
Else
fvalue = datPrimaryRS.Recordset(mfld(x))
End If

I have verified by stepping that isNull is true while moving the cursor
shows
"Steven Moss" in datPrimaryRS.Recordset(mfld(x))

What gives?
Apr 15 '06 #1
2 9168

"Raoul Watson" <Wa*****@IntelligenCIA.com> wrote in message
news:DTY%f.715$UK5.619@trndny01...
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 not Null. Any wild shot or theory as to why this is happening is appreciated. Also, any
other alternative is appreciated since I am stuck and cannot proceed without fixing this issue (tried = Null and that doesn't work either).

Basically, I have a field in a databsse which could be null, so in the code i have:

' mfld(x) would have field names like "NAME" etc.

If IsNull(datPrimaryRS.Recordset(mfld(x))) Then
fvalue = ""
Else
fvalue = datPrimaryRS.Recordset(mfld(x))
End If

I have verified by stepping that isNull is true while moving the cursor
shows
"Steven Moss" in datPrimaryRS.Recordset(mfld(x))

What gives?


That is strange.

The situation where IsNull fails with 'Empty' is well-documented. (ie, some
databases will reset a field (text, nullable) that once had a value back to
Empty or " ", and not Null when the value is deleted), and other issues.

But I have never encountered the situation were a value existed and IsNull
failed. However, the Field.Values in a Recordset collection are Variants and
occasionally a strange dance (or joint psychosis) can develop based on the
Field data type attributes, the SQL used to fetch the data, and whether you
have previous dereferenced the value in your code.

You might go back and see if you can find something unusual about this
particular Field and its history.

There is simple workaround in this case. Use this instead...
fvalue = datPrimaryRS.Recordset(mfld(x)) & ""

hth
-ralph

Apr 15 '06 #2

"Ralph" <nt*************@yahoo.com> wrote in message
news:1u******************************@arkansas.net ...

"Raoul Watson" <Wa*****@IntelligenCIA.com> wrote in message
news:DTY%f.715$UK5.619@trndny01...
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 not Null. Any
wild shot or theory as to why this is happening is appreciated. Also, any other alternative is appreciated since I am stuck and cannot proceed

without
fixing this issue (tried = Null and that doesn't work either).

Basically, I have a field in a databsse which could be null, so in the

code
i have:

' mfld(x) would have field names like "NAME" etc.

If IsNull(datPrimaryRS.Recordset(mfld(x))) Then
fvalue = ""
Else
fvalue = datPrimaryRS.Recordset(mfld(x))
End If

I have verified by stepping that isNull is true while moving the cursor
shows
"Steven Moss" in datPrimaryRS.Recordset(mfld(x))

What gives?


That is strange.

The situation where IsNull fails with 'Empty' is well-documented. (ie,

some databases will reset a field (text, nullable) that once had a value back to Empty or " ", and not Null when the value is deleted), and other issues.

But I have never encountered the situation were a value existed and IsNull
failed. However, the Field.Values in a Recordset collection are Variants and occasionally a strange dance (or joint psychosis) can develop based on the
Field data type attributes, the SQL used to fetch the data, and whether you have previous dereferenced the value in your code.

You might go back and see if you can find something unusual about this
particular Field and its history.

There is simple workaround in this case. Use this instead...
fvalue = datPrimaryRS.Recordset(mfld(x)) & ""

hth
-ralph


Ralph..

You have no idea how valuable your golden advice is!!

Obviously the test for null is needed since we will get an
invalid use of null when we try to assign it to a variable/

Your method however, not only works, but it is a much more
efficient way of coding the isNull test..

Thanks again bud..
Apr 16 '06 #3

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

Similar topics

2
by: eBob.com | last post by:
I've got the basics of an XML ini file working. I.E. I can stash away and retrieve user preferences. (Code below.) But how do I handle a new preference? Say I have A and B. And then I invent...
1
by: Gilz | last post by:
Hi I was wondering if anyone could help i have picked this database up from a designer who has now left the company. The code is a couple of years old. On click of a button it open a...
2
by: Hexman | last post by:
Hello All, In SS EE I have nulls in a column. I want to select and eventually change to a zero (its a smallint column). I've tried selecting 'null', 'dbnull', etc. Then I read about the ISNULL...
9
by: Drum2001 | last post by:
Hello All! I am using the following code during an On Click event for a button. It works properly to create a query for a report: Private Sub Command14_Click() If IsNull(Me.Text30) Then ' No...
6
by: KiwiGenie | last post by:
Hi..I am trying to make a search form. I am fairly new to access and could well be looking at it completely wrong. I have an unbound form with textboxes in the header for entering different search...
0
by: zensunni | last post by:
The script I've made checks for identical fields in the database before it inserts a new row. However, there is one number that, when checked for, will not be found. It assumes there is no row, so it...
13
by: clarencemo | last post by:
Hello, I currently have a split db and I keep getting this error after about a day's worth of use. Once I've repaired it, I have created a clean db, imported the tables from a previous backup and...
4
kcdoell
by: kcdoell | last post by:
Hello: I have the following afterupdate event: Private Sub GWP_AfterUpdate() 'Updates the Total calculation in the control "SumGWP" on the quick reference 'table that is located on the form...
22
by: MLH | last post by:
100 Dim db As Database, rst As Recordset 120 Set db = CurrentDb 140 PString = "SELECT qryBatchList.ReadyFor906, qryBatchList.BatchID FROM qryBatchList WHERE...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

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.