Invalid use of null

[Trudye]

I know this is a very novice question but I just have to ask.

While populating my form, on all of my option and checkbox controls (that do not have data) I am receiving the msg "Invalid use of Null".

I noticed that the checkboxes have Dataformat property and I tried setting it to Boolean. I thought since one of the options was null that would allow the field to accept a null value. Alas it ‘twas not true!

I have 55 fields to fill quite a few of them are option or checkbox. Do I have to check them all for isNull?
Is there some property I can change or some way to define the field to alleviate this problem?

Thanks for taking the time to read this unworthy question.
Queen of the Lazy
Trudye

 

[vbSun]

Dear queen..

How ae you getting the data from the tables? via setting recordset and using the rst.fields(i).value? then, try using rst.fields(i).value & "", while you are assigning to a controls' property.

May be it will solve the problem.

Happy days..

Sunil

 

[Trudye]

Hi Suni:

Thanks for responding so quickly. I'm not sure I understand your solution.

I am moving them one at a time (I'm not indexing) from a recordset resulting from a SELECT statment (i.e. txtFull_Rate = adoRS1!full_rate).

Thanx
Trudye

 

[CajunCenturion]

It sounds like you're trying to assign to a checkbox a value coming from a recordset, but the value in the recordset is null. If this is the case, then you must first check to see if the value in the recordset is null, before assigning it to the checkbox.

If Not (IsNull(rstRS.Fields("fldnam&quot.Value)) Then
assign normally
Else
assign a default value
End If


Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Trudye]

Thanks. I was afraid of that but I was holding out hope. With 55 fields on one form I better get started coding isNull statements.

Thanks again
Trudye

 

[vbSun]

Yea,

what Cajun refers to is the right way of checking things. But my suggestion was to override a checking, by just adding an empty string at the end, so that the database value can never be null. But let me ask you something.. if you have the data to be displayed in a checkbox, and it comes from an AccessDB, how can it be NULL? it cant be null, it holds True/False, or 1/0. Hence I think, the error is in the field definition, where the datatype shud be true/false and not text or anything else. In case you cant change the datatype, check whether there is a place where you assign a NULL?Also check the places where you input the data to the table.

All the very best.
Sunil

 

[ganeshmb]

Hello Trudye
If you are fetching the data from the database as a recordset or individual parameters for the controls on your form, you need to take care of fields that could be Nullable.
To avoid ADODB error 'Invalid use of Null', try appending
"" to all the fields or the fields that you know might contain Null like this:
Text1.Text = rsRecordSet.Fields("Field1&quot.Value & "" or
Text1.Text = rsRecordSet.Fields("Field1&quot.Value & vbNullString.

Hope this helps!

Syed Mateen

 

[Alabaster]

Im going to through this one out there and if I'm wrong my appologies, but in MS Access you would do the following...

text1.text = Nz(adoRS1!text1,"&quot

I'm not sure if the Nz Command is recognized in VB or not, I've never needed to use it in any of my projects.

Also looking at your post I notice you dont specify which property of Text1 you want to assign the adoRS1 value to. Try specifying the property.

Best of Luck

Peter

 

[VBOldTimer]

For integers or booleans:

=Val(Format$(TheField))

For Text:
=Format$(TheField)

Doubles and Singles can be handled like the integer example on a pc set to US standards, otherwise you will need to check for a Null using IsNull() or IsNumeric

=IIf (IsNull(rs(0)),0, rs(0))

 

[Trudye]

Thanks everyone for the feedback.

Someone ask why the fields contained Null values when by definition they should have been boolean (0/1)? This is an old database I got from a user and the fields were defined as TEXT. If option/field was never checked then the field value is null.

Thanks again. I love this site we have the smartest, most courteous and knowledgeable people in the world.

Have a GREAT Easter everyone
Trudye

 

[Merlijn]

If you don't want to code the isnull xty times, use a function.
e.g.

Public Function funNZ(var_Object As Variant, var_Default As Variant) As Variant
'------------------------------------------------------------------------------
' Comments : Checks for Null, and returns default if null. Works on all DataTypes
' Parameters: var_Object As Variant ... Value To Check
' var_Default As Variant .............. Default to return if Null
' Returns: Variant .................... NonNull Value
'
' Example: X = funNZ(rs.fields(3), "test&quot
'------------------------------------------------------------------------------
funNZ = IIf(IsNull(var_Object) Or IsEmpty(var_Object), var_Default, var_Object)
End Function


Merlijn is the name, and logic is my game...

 

[Schroeder]

For me, VBOldTimer's solution is the easiest. Sending your fields through a Format function with no parameters protects nicely against null values.

This practice has gotten me into trouble however. If you're working with a text field that happens to contain only numeric characters, it will remove leading zeroes. Also, it will truncate long strings to 255 characters.

 

[CCLINT]

So the best solution may be to send the recordset Field to a function, and in that function the field's Type is checked, and using using a Select Case you check for each type and set a default value if the value is a NULL.
The result then gets sent back to the caller.

Doing it this way takes care of almost every situtaion with one small function, and you're only having to pass one object to the function - the field.

Public Function FormatFieldValue(fld As ADODB.Field, Optional bFormatForTextBox As Boolean = True) As Variant

Select Case fld.Type
Case adDouble, adSingle, adSmallInt, adInteger
If IsNull(fld.Value) Then
FormatFieldValue = 0
Else
FormatFieldValue = fld.Value
End If

Case adLongVarWChar, adVarWChar
FormatFieldValue = fld.Value & vbNullString

Case Else
FormatFieldValue = fld.Value
End Select

If bFormatForTextBox Then FormatFieldValue =CStr(FormatFieldValue )

End Function

And call it like this:

Text1.Text = FormatFieldValue(rs.Fields("SomeFieldName&quot)