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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.