CODEWITHSUNDEEP
ms-accessvba
Javed Yusuf
Javed Yusuf

Reputation: 45

How to set Date Field to Blank Value

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

Upvotes: 3

Views: 5206

Answers (2)

Gustav
Gustav

Reputation: 55816

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.

Upvotes: 0

Lee Mac
Lee Mac

Reputation: 16015

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.

Upvotes: 1

Related Questions