Adding a date and ending up with the last day of the month

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i'm taking a date from a subform and if certain monies are greater then a
month (or two or three), then getting the date to be the last day of the
month - no matter when it is performed or what the month. if today is
1/5/05, i want the date changed to 1/31/05 if it is null or 2/28/05 if i add
one month. here's what i have
thus far....

dim amntMnths as integer
'got to update paid thru date here....
'[unmonthly] & [RegContr] are currency
If Me![UnMonthly] >= Forms![deposit]![CashHistOrphans4Deposits]![RegContr]
Then
amntMnths = Int(Me![UnMonthly] /
Forms![deposit]![CashHistOrphans4Deposits]![RegContr])
'sometimes the date i want to do the math too is null
'so here i try to give it a current date - sure wish i new how to make that
date the last day of the month
'PaidThru is a date (m/d/yyyy)
If IsNull(Forms![deposit]![CashHistOrphans4Deposits]![PaidThru]) Then
Forms![deposit]![CashHistOrphans4Deposits]![PaidThru] = DateAdd("m", 0,
Date)
'i've changed that 0 to 1 but it's the next month on the day 'it is run, not
the last day of the month
End If
Forms![deposit]![CashHistOrphans4Deposits]![PaidThru] = DateAdd("m",
amntMnths, _
Forms![deposit]![CashHistOrphans4Deposits]![PaidThru])
End If

I sure hope you can help...but thanks for your time...and my gratitude is in
advance.....
 
i solved it with this: (didn't want to expend your time)
Dim dtmDate As Date
Dim dhLastDayInMonth As Date

If Me![UnMonthly] >= Forms![deposit]![CashHistOrphans4Deposits]![RegContr]
Then
amntMnths = Int(Me![UnMonthly] /
Forms![deposit]![CashHistOrphans4Deposits]![RegContr])

If IsNull(Forms![deposit]![CashHistOrphans4Deposits]![PaidThru]) Then
dtmDate = Date
dhLastDayInMonth = DateSerial(Year(dtmDate), Month(dtmDate) + 1, 0)
Forms![deposit]![CashHistOrphans4Deposits]![PaidThru] = DateAdd("m",
amntMnths, _
dhLastDayInMonth)
'Forms![deposit]![CashHistOrphans4Deposits]![PaidThru] = DateAdd("m", 0,
Date)
Exit Sub
End If

dtmDate = Forms![deposit]![CashHistOrphans4Deposits]![PaidThru]
dhLastDayInMonth = DateSerial(Year(dtmDate), Month(dtmDate) + 1, 0)
Forms![deposit]![CashHistOrphans4Deposits]![PaidThru] = DateAdd("m",
amntMnths, _
dhLastDayInMonth)
'Forms![deposit]![CashHistOrphans4Deposits]![PaidThru] = DateAdd("m",
amntMnths, _
Forms![deposit]![CashHistOrphans4Deposits]![PaidThru])
End If
 
Back
Top