• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 405
  • Last Modified:

Update a field based on completion of other fields using VBA in Access

Hi Experts,

I am attempting to update a field such that the field Completed reads 'Yes' when 3 date fields are populated and 'No' when any one of the 3 date fields are not populated.  I read that you can't test for Null values unless you convert the variable to a variant so that's what I've tried to do below.

 The code below returns 'No' even when the 3 date fields are populated, what am I doing wrong?


Private Sub Form_AfterUpdate()
    Dim vconvert As Variant
    Dim vconvert2 As Variant
    Dim vconvert3 As Variant
    Dim BValue As Boolean
    Dim BValue2 As Boolean
    Dim BValue3 As Boolean
    Dim sSQL As String
    Dim iID As Integer
    iID = Me.ID.Value
    vconvert = CVar(Me.DATE_AUTHORIZED)
    vconvert3 = CVar(Me.DATE_RELEASED)
    BValue = IsNull("vconvert")
    BValue2 = IsNull("vconvert2")
    BValue3 = IsNull("vconvert3")
    If BValue And BValue2 And BValue3 = False Then
        sSQL = "Update Main " & _
               "SET [Completed]= 'Yes' " & _
               "WHERE Main.[ID] = " & iID & ";"
        DoCmd.SetWarnings False
        DoCmd.RunSQL (sSQL)
        sSQL = "Update Main " & _
               "SET [Completed]= 'No' " & _
               "WHERE Main.[ID] = " & iID & ";"
        DoCmd.SetWarnings False
        DoCmd.RunSQL (sSQL)
    End If
End Sub
1 Solution
Rey Obrero (Capricorn1)Commented:
why not just do it using a query..

Update Main
Set [Completed]=IIf([DATE_AUTHORIZED] & ""<>""  and [DATE_COMPLETED_FOR_RELEASE] & ""<>"" and [DATE_RELEASED] & ""<>"","Yes","No")

Guy Hengel [angelIII / a3]Billing EngineerCommented:
I agree with above.
Just time highlight the issue in your code:
= IsNull("vconvert")
Should be:
= IsNull(vconvert)
Gustav BrockCIOCommented:
You can reduce this a lot:
Private Sub Form_AfterUpdate()

    Dim bNotCompleted As Boolean
    Dim sCompleted As String
    Dim sSQL As String
    Dim iID As Integer
    iID = Me!ID.Value
    bNotCompleted = _
        IsNull(Me!DATE_AUTHORIZED) Or _
    sCompleted = Format(Not bNotCompleted, "Yes/No")

    sSQL = "Update Main " & _
           "SET [Completed]= '" & sCompleted & "' " & _
           "WHERE Main.[ID] = " & iID & ";"
    DoCmd.SetWarnings False
    DoCmd.RunSQL (sSQL)
    DoCmd.SetWarnings True

End Sub 

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now