Null Values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Why do Access forms crap out when trying to assertain the value of a filed
containing nulls, and how do you get around this?

For example, I might pull data from SQL server using an ADO connection string.
When I hit a null field, VB goes into error mode. You can't even say if such
and such is null because it will give you an error message about nulls.
(e-mail address removed)
 
Hi,


The only data type that can accept a NULL is a variant.


A control VALUE ( Me.ControlName.Value, or Me.ControlName in most cases
since Value is the default property) is a Variant and thus, does not create
any problem. This is different from Classical VB6 where the default property
is Text, and a STRING data type.

If you use a variable like


Dim x As Long
x=MyRecordset.Fields("FieldName")


clearly, you are in a situation where you can get into a big problem: if
there is a NULL as value. x, being LONG, NOT a variant, won't be able to
handle it! If you absolutely want to keep the Long, as data type, then try:


x=Nz( MyRecordset.Fields("FieldName"), 0 )


which will replace the NULL by a zero before your Long sees it.


Another possible problem is when you use a procedure call:

Public Sub Something( Arg As Long)


clearly, you have the same problem with

Something rst.Fields("FieldName" )


You can also test a value about its Null with IsNull:


If IsNull( rst.Fields("FieldName") ) Then


Maybe you have hit another problem too, but typically, that is what REALLY
goes on when someone claim that it seems that "Access forms crap out" :-)



Hoping it may help,
Vanderghast, Access MVP
 
Thanks. I worked out the vull by filling an empty string if its null.

It didn't work when I said
If Rval is null
but it does work if I say,

isnull(Rval) then Rval = ""

I guess VB can refer to a null val only as an argument within the parameter
isnull( )
(e-mail address removed)
 
Hi,


In VBA, it is in one word,

If IsNull(valueToTest ) Then

but in SQL it is (preferably) in two words:

WHERE fieldName IS NULL



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top