Last day of month

[???? ??????]

To get the last day of the month, I have sometimes simply ADDED 1 month
to the date and then SUBTRACTED 1 day from the result.

 

[Guest]

I am trying to pass a date in code, add calculated months (from 1 - 7) to it
and write the new date with the day field being the last day of that month.
I've read an ms document (#210604) showing how to use the dateserial
function. I've used it but am finding it is not coming back with accurate
dates! A variable should have been returning 10/31/05 was coming back
10/30.05. I also had the same thing happen in the month of August. It was
coming back with 8/30/05 not 8/31/05. I tried different years, still
happened. Here is my code:

Dim amntMnths2 As Integer
amntMnths2 =
Int(Forms![deposit]![CashHistOrphans4Deposits]![ContrBaL] /
Forms![deposit]![CashHistOrphans4Deposits]![RegSend])
dtmDate =
Forms![deposit]![CashHistOrphans4Deposits]![PaidThru]
dhLastDayInMonth = DateSerial(Year(dtmDate), Month(dtmDate)
+ 1, 0)
Forms![deposit]![CashHistOrphans4Deposits]![PaidThru] = DateAdd("m",
amntMnths2, _
dhLastDayInMonth)

I think the problem is in the "+1" (in the dateserial line), but I am not
sure, as the ms documentation says "for the next month" - it does not say for
as many months as you want to add. Any thoughts on this?

Thanks for sharing you experience and your time!

 

[Rick Brandt]

I am trying to pass a date in code, add calculated months (from 1 -
7) to it and write the new date with the day field being the last day
of that month. I've read an ms document (#210604) showing how to use
the dateserial function. I've used it but am finding it is not
coming back with accurate dates! A variable should have been
returning 10/31/05 was coming back 10/30.05. I also had the same
thing happen in the month of August. It was coming back with 8/30/05
not 8/31/05. I tried different years, still happened. Here is my
code:

Dim amntMnths2 As Integer
amntMnths2 =
Int(Forms![deposit]![CashHistOrphans4Deposits]![ContrBaL] /
Forms![deposit]![CashHistOrphans4Deposits]![RegSend])
dtmDate =
Forms![deposit]![CashHistOrphans4Deposits]![PaidThru]
dhLastDayInMonth = DateSerial(Year(dtmDate),
Month(dtmDate) + 1, 0)
Forms![deposit]![CashHistOrphans4Deposits]![PaidThru] = DateAdd("m",
amntMnths2, _
dhLastDayInMonth)

I think the problem is in the "+1" (in the dateserial line), but I am
not sure, as the ms documentation says "for the next month" - it does
not say for as many months as you want to add. Any thoughts on this?

Thanks for sharing you experience and your time!

For DateSerial to give you the last day of a given month you actually ask it
for the zeroth day of the next month. That is why you add one to the month
and use zero for the day argument so your current syntax is correct.

I didn't bother to figure out all of the other stuff that is going on with
your expression, but you are using DateSerial properly if you want the last
day of a month. Any time the last argument is zero you are guaranteed to
get the last day of "some" month. You are losing a day someplace else in
there.

 

[Ron2005]

To get the last day of the month, I have sometimes simply ADDED 1 month
to the date and then SUBTRACTED 1 day from the result.

 

[Guest]

so you don't think the ms function could be wrong?

I am trying to pass a date in code, add calculated months (from 1 -
7) to it and write the new date with the day field being the last day
of that month. I've read an ms document (#210604) showing how to use
the dateserial function. I've used it but am finding it is not
coming back with accurate dates! A variable should have been
returning 10/31/05 was coming back 10/30.05. I also had the same
thing happen in the month of August. It was coming back with 8/30/05
not 8/31/05. I tried different years, still happened. Here is my
code:

Dim amntMnths2 As Integer
amntMnths2 =
Int(Forms![deposit]![CashHistOrphans4Deposits]![ContrBaL] /
Forms![deposit]![CashHistOrphans4Deposits]![RegSend])
dtmDate =
Forms![deposit]![CashHistOrphans4Deposits]![PaidThru]
dhLastDayInMonth = DateSerial(Year(dtmDate),
Month(dtmDate) + 1, 0)
Forms![deposit]![CashHistOrphans4Deposits]![PaidThru] = DateAdd("m",
amntMnths2, _
dhLastDayInMonth)

I think the problem is in the "+1" (in the dateserial line), but I am
not sure, as the ms documentation says "for the next month" - it does
not say for as many months as you want to add. Any thoughts on this?

Thanks for sharing you experience and your time!

For DateSerial to give you the last day of a given month you actually ask it
for the zeroth day of the next month. That is why you add one to the month
and use zero for the day argument so your current syntax is correct.

I didn't bother to figure out all of the other stuff that is going on with
your expression, but you are using DateSerial properly if you want the last
day of a month. Any time the last argument is zero you are guaranteed to
get the last day of "some" month. You are losing a day someplace else in
there.

 

[Rick Brandt]

so you don't think the ms function could be wrong?

No. Have you tried it in an independant test? Just enter the following in
the immediate window.

?DateSerial(2005, 11, 0) <Enter>

When I do this I get 10/31/2005. If you are getting 10/30/2005 then either
the month number being fed to DateSerial is not 11 or else something is
subtracting a day after-the-fact.

 

[Guest]

thanks rick.... i eventuall got it........
dhLastDayInMonth = DateSerial(Year(dtmDate), Month(dtmDate)
+ 1, 0)
i replaced the +1 with a variable.....if it's to be next month it should be
+2, if it's to two months= +3,3months=+4, etc.....so i pass it a variable
with one more month (number) then i want, and it works fine........i
appreciate your help.......i sure wish it was a little more documented....