Determining the value of 3 Date/Time Boxes....Newbie

[REAbernathy]

I imported a excel spreadsheet into an access datebase and in the excel spreadsheet there are 3 fields that allow the user to click inside the box and add a date (KBtoFirms1_CMAS + KBFirms2_CMAS + KBFirms3_CMAS = BatchKBCount_CMAS_DontTouch). On my form i have added 3 text boxes that allow the user to enter a date. What I am trying to do is assign a value to the fields and when a date is added count that as "1" and when the field is empty count that as "0" then place that value in the BatchKBCount_CMAS_DontTouch. For example if KBtoFirms1_CMAS has a date and KBtoFirms2_CMAS has a date and KBtoFirms3_CMAS does not have a date then the value in BatchKBCount_CMAS_DontTouch should equal "2". Can this be done by code or can i do this by using sometype of forumla in the table and if so how do i accomplish this.

Thanking you in advance for your help and assistance in this matter.

[GeekInOhio]

Putting the following in the Before Update events of the three KBtoFirms date fields should do you fine.

Dim myCount As Integer


myCount = 0


If IsDate(Me.KBtoFirst1_CMAS) Then
    myCount = myCount + 1
End If


If IsDate(Me.KBtoFirst2_CMAS) Then
    myCount = myCount + 1
End If


If IsDate(Me.KBtoFirst3_CMAS) Then
    myCount = myCount + 1
End If


Me.BatchKBCount_CMAS_DontTouch = myCount

[REAbernathy]

GeekinOhio

Thanks for the quick response however you said add it Before Update events and I am a bit confused about this being that I only have 2 command buttons on my form Save Record and Next Record. Remember I am a Newbie to VBA......

[GeekInOhio]

Add it to the Before Update event of the three text boxes. If you want an initial count on record load, you can also add the code to the On Current event of the form.