NULL FIELDS!!!

[VBonliner]

How can I read a NULL field from ACCESS into
a control in VB without getting Run Time
Error 94 (Invalid use of Null).

Example: Text1 = !Field1
When Field1 is 'empty' I get Run Time Error 94.

Help!

[RealisticGraphics]

This is where "error handling" comes in handy. Add something like the following to your code.

On Error Goto CheckError

Text1 = !Field1

Exit Sub
CheckError:
If Err.Number = 94 Then
  Text1 = "" 'Or whatever you want it to return to the user.
Else
  Msgbox Err.Number & ":" & vbCrLf & Err.Description, "Error"
End If
End Sub

This should get you out!

[VBonliner]

Tnanks http://www.RealisticGraphics.com

[Cenobite]

My favourite way of getting around this is as follows:
Text1 = "" & !Field1

This way you are never assigning a null value.

[xmin]

Another way:

If IsNull(!Field1) then Text1.Text=""

[RealisticGraphics]

xmin:
When I use the "IsNull()" function in this particular area I get the same error. Non of the function that deal with Nulls seem to work when working in conjunction the the access database as we are using it here. Maybe it's my system, who knows.

[xmin]

Hi, RealisticGraphics. For your message, I made a special attempt at the following code and it has worked quite well. Could you show your code?

Code
______________________________________________________
Option Explicit
Dim wsCmptXm As Workspace
Dim dbCmptXm As Database
Dim rsXmnee As Recordset

Private Sub Command1_Click()

'MoveNext Button

With rsXmnee
.MoveNext
If .EOF Then .MoveLast
Text1.Text = !Tihao ¡®Tihao is a Byte field holding the numbers 0 - 255
If IsNull(!Defen) Then ¡®Defen is a Single field
Text2.Text = "Null Field"
Else
Text2.Text = !Defen
End If
If IsNull(!Beizhu) Then ¡®Beizhu is a Text field
Text3.Text = "Null Field"
Else
Text3.Text = !Beizhu
End If
End With

End Sub

Private Sub Command2_Click()

'MovePrevious Button

With rsXmnee
.MovePrevious
If .BOF Then .MoveFirst
Text1.Text = !Tihao
If IsNull(!Defen) Then
Text2.Text = "Null Field"
Else
Text2.Text = !Defen
End If
If IsNull(!Beizhu) Then
Text3.Text = "Null Field"
Else
Text3.Text = !Beizhu
End If
End With

End Sub

Private Sub Command3_Click()

'Close Button

rsXmnee.Close
dbCmptXm.Close
wsCmptXm.Close
End

End Sub

Private Sub Form_Load()

Set wsCmptXm = DBEngine.Workspaces(0)
Set dbCmptXm = wsCmptXm.OpenDatabase(App.Path & "\CmptXm.mdb")
Set rsXmnee = dbCmptXm.OpenRecordset("Xmnee", dbOpenDynaset)
With rsXmnee
.MoveFirst
Text1.Text = !Tihao
If IsNull(!Defen) Then
Text2.Text = "Null Field"
Else
Text2.Text = !Defen
End If
If IsNull(!Beizhu) Then
Text3.Text = "Null Field"
Else
Text3.Text = !Beizhu
End If
End With

End Sub
_____________________________________________________
Visual Basic Professional 6.0

[Edited by xmin on 09-30-2000 at 04:37 AM]

[RealisticGraphics]

Tried using that code (modified a bit) and got the same result. The modified portion is that I made it use a data control as I had previously, so I connected using dao with no data control as you did and it worked fine. It must be something with the data control, I thought... I reinstalled VB and now both ways work. Fluke, I guess.

[S@NSIS]

Hi,
Nothing wrong with xmin's code but you might find that this looks better. (shortens your code too )

'Old code
'If IsNull(!Defen) 
'Text2.Text = "Null Field" 
'Else 
'Text2.Text = !Defen 
'End If

'new code
Text2.Text = IIf(IsNull(!Defen),"Null Field",!Defen)

Hope this helps

Shaun

[xmin]

Very good, S@NSIS!

[stickleprojects]

Hi,

I find the following method works:
mycontrol.text = rs(fieldname) & ""

also,
try the following to check for nulls and empty strings at the same time:
if len(rs(fieldname) & "")=0

hope this helps

[flewis]

I always use this one:

If !Name <> "" Then
txtName = !Name
End If


I post this b/c it seems simple, but there were so many other methods posted. Is there a speed consideration to use the If/Then statements when dealing with null values?

[BruceG]

flewis:
FYI, testing for a zero-length string ("") is not the same as testing for Null. I tend to use the method posted by S@NSIS:
Text1.Text = IIf(IsNull(!MyField), "", !MyField)