How to set Date Field to Blank Value

Question

I am trying to reset value of date to null and value of weight to null.

I tried the following code, but only weight is being set to 0 and date is not set to null:

Private Sub Form_Load()
    Me.Text42.Value = ""

    Dim i As Integer
    Dim Db6 As Database
    Dim Rs6 As Recordset
    Dim Trn6 As String
    Set Db6 = CurrentDb
    Set Rs6 = Db6.OpenRecordset("GoatMasterTable")

    Do While Not Rs6.EOF
        If Rs6.Fields("Recentweight") > 0 Then
            Rs6.Edit
            Rs6.Fields("RecentWeight") = 0
            Rs6.Fields("RecentWeightDate") = """" Or IsNull(Rs6!RecentWeightDate)
            Rs6.Update
        End If
        Rs6.MoveNext
    Loop

    Rs6.Close
    Set Rs6 = Nothing
    Db6.Close
End Sub

First: Get familiar with [Null](http://www.baldyweb.com/nullemptyetc.htm). Second: Don't use`db.Close`! You wanted`Set db = Nothing`, but this isn't necessary here (but doesn't hurt too much) as [VBA Counts Object Refernces of Instances](https://stackoverflow.com/questions/51065566/what-are-the-benifits-of-setting-objects-to-nothing/51066727#51066727) and destroys them if they are out of scope. Third: use @LeeMacs answer! — ComputerVersteher, Jan 12 '19 at 19:31

Lee Mac · Answer 1 · 2019-01-12T19:22:09.087

Since you are not performing record-dependent calculations and are merely setting each record to the same value, an easier method would be to simply execute a SQL statement such as:

update GoatMasterTable set RecentWeight = 0, RecentWeightDate = Null where RecentWeight > 0

As such, your function might become:

Private Sub Form_Load()
    Me.Text42 = ""
    With CurrentDb
        .Execute "update GoatMasterTable set RecentWeight = 0, RecentWeightDate = Null where RecentWeight > 0"
    End With
End Sub

You will also need to ensure that the RecentWeightDate field in your GoatMasterTable allows null values.

score 0 · Answer 2 · answered Jan 12 '19 at 21:53

0

All you need is to set the date field to Null:

Rs6.Fields("RecentWeight").Value = 0
Rs6.Fields("RecentWeightDate").Value = Null

Of course, field RecentWeightDate of the table must allow Null values.

answered Jan 12 '19 at 21:53

Gustav

53,498
7
29
55

How to set Date Field to Blank Value

2 Answers2