Hello ppl, new to this forum, perhaps you guys/gals can shed some light on my predicament.
I have an option button "30 days Late" when the user clicks this should take the date and add 30 days to it, to determine if the payment is 30 days late. I'm going to give you the code below. Some background on the logic. Each field in the database is assigned an acronym that I cannot change, SCHD_DT1 thru SCHD_DT14 are formatted in the short date form. ACT_PAY1 thru ACT_PAY14 are Currency with the default as "$0.00". If the the payment dates are 30 days past the SCHD_DT(?) then display the record on a seperate form. My problem is that it's not( or I'm not writing the code correctly) finding the date that is 30 days past the test database record. Any help would be appreciated
thx
Larson

 Private Sub opt30_Click()
'declare a string variable to hold the date + 30days
Dim strCheckDate As Date


'declare a boolean value to determine if the date is true or false
Dim blnDate As Boolean

'declare a variable to hold a string for spacing
Dim myString
    myString = Space(10)

'declare an integer variable to hold the index of the PayArray
Dim z As Integer
    z = 0
'declare an array and populate
    Dim PayArray(0 To 13) As Date
        PayArray(0) = adoVAT.Recordset.Fields("SCHD_DT1").Value
        PayArray(1) = adoVAT.Recordset.Fields("SCHD_DT2").Value
        PayArray(2) = adoVAT.Recordset.Fields("SCHD_DT3").Value
        PayArray(3) = adoVAT.Recordset.Fields("SCHD_DT4").Value
        PayArray(4) = adoVAT.Recordset.Fields("SCHD_DT5").Value
        PayArray(5) = adoVAT.Recordset.Fields("SCHD_DT6").Value
        PayArray(6) = adoVAT.Recordset.Fields("SCHD_DT7").Value
        PayArray(7) = adoVAT.Recordset.Fields("SCHD_DT8").Value
        PayArray(8) = adoVAT.Recordset.Fields("SCHD_DT9").Value
        PayArray(9) = adoVAT.Recordset.Fields("SCHD_DT10").Value
        PayArray(10) = adoVAT.Recordset.Fields("SCHD_DT11").Value
        PayArray(11) = adoVAT.Recordset.Fields("SCHD_DT12").Value
        PayArray(12) = adoVAT.Recordset.Fields("SCHD_DT13").Value
        PayArray(13) = adoVAT.Recordset.Fields("SCHD_DT14").Value



Dim i As Integer

    i = 0
'declare integer to hold array index


    Dim ActPay(0 To 13) As Currency

        ActPay(0) = adoVAT.Recordset.Fields("ACT_PAY1").Value
        ActPay(1) = adoVAT.Recordset.Fields("ACT_PAY2").Value
        ActPay(2) = adoVAT.Recordset.Fields("ACT_PAY3").Value
        ActPay(3) = adoVAT.Recordset.Fields("ACT_PAY4").Value
        ActPay(4) = adoVAT.Recordset.Fields("ACT_PAY5").Value
        ActPay(5) = adoVAT.Recordset.Fields("ACT_PAY6").Value
        ActPay(6) = adoVAT.Recordset.Fields("ACT_PAY7").Value
        ActPay(7) = adoVAT.Recordset.Fields("ACT_PAY8").Value
        ActPay(8) = adoVAT.Recordset.Fields("ACT_PAY9").Value
        ActPay(9) = adoVAT.Recordset.Fields("ACT_PAY10").Value
        ActPay(10) = adoVAT.Recordset.Fields("ACT_PAY11").Value
        ActPay(11) = adoVAT.Recordset.Fields("ACT_PAY12").Value
        ActPay(12) = adoVAT.Recordset.Fields("ACT_PAY13").Value
        ActPay(13) = adoVAT.Recordset.Fields("ACT_PAY14").Value

Dim k As Integer

        For k = 0 To adoVAT.Recordset.RecordCount - 1

            strCheckDate = PayArray(z) + 30

             If ActPay(i) = "$0.00" Then
                blnDate = True



                Dim str30Delinquent As String
                str30Delinquent = adoVAT.Recordset.Fields("FName") & myString & adoVAT.Recordset.Fields("LName") & myString & vbNewLine & adoVAT.Recordset.Fields("ADDRESS") & _
                adoVAT.Recordset.Fields("CITY") & myString & adoVAT.Recordset.Fields("STATE") & " " & adoVAT.Recordset.Fields("ZIP") & vbNewLine & _
                "Message text goes here to reflect severity" & vbNewLine

                Dim str30SQL As String

                    str30SQL = "Select * from Students where " & ActPay(i) & "=$0.00 ORDER BY FName"

                    adoVAT.RecordSource = str30SQL

                    HFlexGrid1.Refresh



                    'add person to the label caption on the new form
                    frm30Days.lbl30Days.Caption = str30Delinquent

            Else
                    blnDate = False
                    MsgBox "There are No Late Payments today", vbOKCancel

            End If

        Next
        adoVAT.Recordset.MoveNext

        'show the form
        frm30Days.Show


End Sub

I think.....therefore..I code

__________________
I think.....therefore..I code

Time for a quick answer,

Try using the DateAdd() function:

   ' Replace
   strCheckDate = PayArray(z) + 30

   ' With
   strCheckDate = DateAdd("d",30,PayArray(z))

I like the naming conventions used for variable declaration. Such as str, int, and the like. And I commend your use/consistency of use. Just, one suggestion instead of using str on a date variable try using dtm for the prefix and save str for string variables.

You may also want to verify that PayArray(z) is a valid date. You can use the IsDate() function:

   If IsDate(PayArray(z)) = True Then
        ' Add 30 days to the date
        dtmCheckDate = DateAdd("d",30,PayArray(z))
   Else
        ' Invalid date
        ' Do some error handling

   End If

Hope this helps.





Larry Asher

Thanks for your help, you have no idea how much this helps!:D
Larson

I think.....therefore..I code