Hi guys
I have a personal financial database that I want to use VB code for to predict when bills are due. I have figured out how to update weekly, fortnightly and monthly expenses but the trimonthly bills are what I am stuck on as they are unique. I need to filter my tblRegularBills table to find the trimonthly records and then use maybe dateserial to use the same day, add three months and clock over another year if necessary. This pretty much what I have for monthly bills and so it needs work but I don't know what help to search for so any tips you can give is a bonus! Thanks!
Andrew
Dim CurrentDate As Date
Dim DateString, BillName, Append_trimth As String
Dim NextDate, BillDate As Date
Dim count As Integer
Dim BillsToAppend As String
count = 0
Do
count = count + 1
Append_trimth = "INSERT INTO tblBillTracking ( Bill, Amount, ExpectBillDate, [Bill Frequency], Account ) SELECT tblBillsRegular.Bill, tblBillsRegular.Amount, KnownBillDate +" & count & " AS [Date], tblBillsRegular.Regularity, tblBillsRegular.Account FROM tblBillsRegular WHERE (((tblBillsRegular.Regularity) Like 'trimonthly' ));"
check = [KnownBillDate] + count
'dateserial(,count
If Day(check) <> Day([KnownBillDate]) Then
GoTo NextLoop5
End If
DoCmd.RunSQL Append_trimth
NextLoop5:
Loop Until check >= Now
I have a personal financial database that I want to use VB code for to predict when bills are due. I have figured out how to update weekly, fortnightly and monthly expenses but the trimonthly bills are what I am stuck on as they are unique. I need to filter my tblRegularBills table to find the trimonthly records and then use maybe dateserial to use the same day, add three months and clock over another year if necessary. This pretty much what I have for monthly bills and so it needs work but I don't know what help to search for so any tips you can give is a bonus! Thanks!
Andrew
Dim CurrentDate As Date
Dim DateString, BillName, Append_trimth As String
Dim NextDate, BillDate As Date
Dim count As Integer
Dim BillsToAppend As String
count = 0
Do
count = count + 1
Append_trimth = "INSERT INTO tblBillTracking ( Bill, Amount, ExpectBillDate, [Bill Frequency], Account ) SELECT tblBillsRegular.Bill, tblBillsRegular.Amount, KnownBillDate +" & count & " AS [Date], tblBillsRegular.Regularity, tblBillsRegular.Account FROM tblBillsRegular WHERE (((tblBillsRegular.Regularity) Like 'trimonthly' ));"
check = [KnownBillDate] + count
'dateserial(,count
If Day(check) <> Day([KnownBillDate]) Then
GoTo NextLoop5
End If
DoCmd.RunSQL Append_trimth
NextLoop5:
Loop Until check >= Now
