the infamous null

IsNull

When assigning a variable to a recordset field you can do the following:

If it is a text field:
strName=rs("Name") & vbNullString

If it is a number, date OR text field:
If Not IsNull(rs("DateBooked")) then
strDate=rs("DateBooked")
End IF
You could also use a variant, which accepts Nulls:

vFieldValue=rs("DateBooked")

You may consider using a function that gets the data from your recordset for each field checks ,which checks for Nulls, or does any needed formating, (by checking the field type):

Public Function GetFieldValue (rs As Recordset, ByVal sField As String) As String

If Not IsNull(rsField)) Then
GetFieldValue =rs(sField)

Else
GetFieldValue =vbNullString
End IF

End Function

This is a very simpleexample, but gives the idea.
I would use a variant as a return value, and in the function check for the field type, (rs(rsField).Type, check if the field has data, and using a Select Case on the field type format the return value, (vbNullString for Text and Date fields, 0 for Number fields, False for Boolean, etc.)

HI,

Suppose ur getting a value from table,
Consider the field name as empname.
Take a variable.

tmp_var_empname=iif(isnull(rs.fields("empname")),"0",rs.fields("empname"))

The result of the variable will be either the data from the field or 0

With regards
S.Satheesh

Hi

if you mean the error that you get when doing

TextBox1.Text = RS.Fields("Name")

when Name is null in the table.

I use this function.

Public Function NulltoString(strInput As Variant)

If IsNull(strInput) Then
NulltoString = ""
Else
NulltoString = strInput
End If

End Function

like this

TextBox1.Text = NulltoString( RS.Field("Name"))

This way I get a valid string containing the value of what is in Name or an empty stringif Name was NULL.

May be I should rename the function FieldtoString.

Cheers
Pierre

On Answer #4 - you can do the same thing with:

TextBox1.Text = RS.Fields("Name") & ""

Adding a blank string to the value will turn any null value to a blank string, and will leave any text value as is.

This question was auto closed due to inactivity