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

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.....
 
G

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Message box with days of month & date. 1
Excel VBA to go to specific sheet & cell in the current workbook not working. 2
Calendar problem selecting last day of month 1
Find "Last Friday" function works almost 100% 10
Month problem 2
Date Validation - First 7 days of the month 6
DATE SELECTION ISSUE 4
Default date to 1st day of the Month 5

Top