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!
Printable View
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!
This is where "error handling" comes in handy. Add something like the following to your code.
This should get you out!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
My favourite way of getting around this is as follows:
Text1 = "" & !Field1
This way you are never assigning a null value.
Another way:
If IsNull(!Field1) then Text1.Text=""
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.
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]
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.
Hi,
Nothing wrong with xmin's code but you might find that this looks better. (shortens your code too :))
Hope this helpsCode:'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)
Shaun
Very good, S@NSIS!
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
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?
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)