Null Not Being Seen - What?

[PSUIVERSON]

I have the following excerpt of some code:

Do
temp = rst!FIELD1
If temp Is Null Then
temp = 0
temp = FormatNumber(temp, 2)
rst.Edit
rst!FIELD1 = temp
rst.Update
ElseIf temp > 100 Then
temp = FormatNumber(rst!FIELD1 / 100, 2)
rst.Edit
rst!FIELD1 = temp
rst.Update
End If

rst.MoveNext
Loop Until rst.EOF

* I am trying to go through a table of 30k records or so and if there is nothing in the field then put it as "0" and show the number "0". Well right now about 80% of the fields are "NULL". When I run this temp = Null but when I toggle and look at the value in temp for the first IF CONDITION it skips right over it? Why doesn't that work? It's clearly NULL but it skips right over it.

Please help a rookie.

 

[PSUIVERSON]

With that code it actually says:

"Object Required" at that line. I don't get it.

 

[hwkranger]

try:

Private Sub FillFields_Click()
On Error GoTo Err_FillFields_Click

Dim db As Database
Dim myRecordSet As Recordset

Set db = CurrentDb()
Set myRecordSet = db.OpenRecordset("query/table where your data is coming from")

If myRecordSet.BOF = True Then Exit Sub
With myRecordSet
    If .RecordCount Then
        .MoveFirst
        Do Until myRecordSet.EOF                
        If ![name of your field] = "" Or IsNull(!Opening)Then   
        .Edit
         ![name of your field]= "0"
        .Update
        End If      
        myRecordSet.MoveNext
        Loop
    End If
End With




myRecordSet.Close
Set myRecordSet = Nothing

db.Close
Set db = Nothing

Exit_FillFields_Click:
    Exit Sub

Err_FillFields_Click:
    MsgBox Err.Description
    Resume Exit_FillFields_Click
    
End Sub

I have something almost exactly the same as this in one of my db's and it works... hope this helps. Cruz'n and Booz'n always.
This post shows what little I do at work.

 

[PSUIVERSON]

Thanks I'll let you know how I make out...

 

[Darrylles]

Hiya,

Be aware of the usual problem with Access where an 'empty' field is not necessarily = NULL, it could be zero and vice-versa.

I've never got a complete answer to this particular problem.

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[PSUIVERSON]

Yeah but when I highlight over the rst!FIELD when doing a TOGGLE BREAKPOINT the value says NULL - You know what I mean?

I'll try locals window and see what it says...

 

[hwkranger]

oops that (!Opening) is supposed to be the name of your field Sorry that came straight from my code ;-)

code should look like

If ![name of your field] = "" Or IsNull(![name of your field])Then


sorry ;-)

as i said, i had something very very similiar in my own code. i just cut it from what i had and thought I changed the variables for you.

Cruz'n and Booz'n always.
This post shows what little I do at work.

 

[PSUIVERSON]

Yeah - I'm trying to get out of an infinite loop from hell right now - so I'll let you know if it works...thanks for the help...I always appreciate it...

 

[cmmrfrds]

Certain variables cannot contain null. The only data type that can contain null or manipulate with null is the variant data type which is actually an object of a sorts. Now, a database field of various data types can contain null but this is different than a variable defined in vba code.

If the variable "temp" is defined as variant you should be able to check for NULL.

 

[PSUIVERSON]

I have temp as a variant.

 

[cmmrfrds]

use this syntax
Dim temp as variant
temp = Null
If IsNull(temp) Then
Debug.Print "is null = "; temp
End If

You can check an Object for null with this syntax
OR
a database field but not a vba variable.
If MyObject Is Null Then