Date + 1 month

  • Thread starter Thread starter Jacob Frankham
  • Start date Start date
J

Jacob Frankham

Hi

How would I add a month to a date in VBA?

And I don't mean [myDate]+31 because there are not always 31 days in a month

I have 2 textboxes, one defaults to the first of the month, I wish to set
the other to the last day of the same month
(could be 28, 30 days etc)

Cheers

J
 
Jacob Frankham said:
Hi

How would I add a month to a date in VBA?

And I don't mean [myDate]+31 because there are not always 31 days in a month

I have 2 textboxes, one defaults to the first of the month, I wish to set
the other to the last day of the same month
(could be 28, 30 days etc)
Click to expand...

Open a file and press Ctl-G to bring up the VBE window. In the immediate
pane, type DateAdd and then while making sure your cursor is inside that
word press F1. The help topic explains all in detail.
 
How would I add a month to a date in VBA?

And I don't mean [myDate]+31 because there are not always 31 days in a month

I have 2 textboxes, one defaults to the first of the month, I wish to set
the other to the last day of the same month
(could be 28, 30 days etc)

Cheers
Click to expand...

To add a month to a date you may do it in this way:

if Date means ... date to which you want to add a month
you may use functions such as year, month, day to receive
Date plus one month:
Date_next=date(year(Date);month(Date+1); Day(Date)),
when 'date' is a function of VBA.
Of course to receive the last day of the month we can use:
Last_Day=date(year(Date);month(Date)+1;1)-1


I think this should work...

rgrs (or pzdr)
Marcin
 
Thanks v much !!!

J
How would I add a month to a date in VBA?

And I don't mean [myDate]+31 because there are not always 31 days in a month

I have 2 textboxes, one defaults to the first of the month, I wish to set
the other to the last day of the same month
(could be 28, 30 days etc)

Cheers
Click to expand...

To add a month to a date you may do it in this way:

if Date means ... date to which you want to add a month
you may use functions such as year, month, day to receive
Date plus one month:
Date_next=date(year(Date);month(Date+1); Day(Date)),
when 'date' is a function of VBA.
Of course to receive the last day of the month we can use:
Last_Day=date(year(Date);month(Date)+1;1)-1


I think this should work...

rgrs (or pzdr)
Marcin
.
Click to expand...
Click to expand...
 
Of course to receive the last day of the month we can use:
Last_Day=date(year(Date);month(Date)+1;1)-1
Click to expand...

or DateSerial(Year(SomeDate), Month(SomeDate)+1, 0)

Remember that Date() or Date is a VBA function that returns today's date.
It's a bad name for a field, an illegal name for a variable, and an awful
name for a custom procedure or function.


Tim F
 
Thanks Ken

What about if I wanted to deduct 1 month?

I tried DateAdd("m", -1 [myDate]) on a date set to 30.09.03 but it gave me
30.08.03 (31 days is Aug)

Any ideas

Cheers!

Jake
Ken Snell said:
DateAdd("m", 1, [myDate])

--
Ken Snell
<MS ACCESS MVP>

Jacob Frankham said:
Hi

How would I add a month to a date in VBA?

And I don't mean [myDate]+31 because there are not always 31 days in a month

I have 2 textboxes, one defaults to the first of the month, I wish to set
the other to the last day of the same month
(could be 28, 30 days etc)

Cheers

J
Click to expand...
Click to expand...
 
One month back from 30 September is 30 August, not 31 August.

If you want the last day of the previous month, you could use this
expression:

DateSerial(Year(Date()), Month(Date()), 0)

But that won't give you a "31-day difference" for all dates.

I tried playing with some variations of date calculations, trying to use the
number of days that the current date is from the last day of the current
month, and then applying that to the previous month, but when the two months
have different number of days, the resulting date is too far back or not far
back. I can envision setting up a complicated expression that compares the
number of days in each of the months, and then setting an upper and lower
limit to the one-month back date, but I'm not convinced that it would
work...and you'd need to make a decision about the logic to be used:
namely, do you always count the number of days that the current date is from
the first of the month, or from the last of the month? You can get a
different answer depending upon which direction you go.

If you can identify which date you want as the result (when going back one
month) for the following examples, I probably can come up with an expression
that will work for you:

Start Dates
---------------

12/31/2003

2/24/2003

10/2/2003

3/11/2003

--
Ken Snell
<MS ACCESS MVP>


Jacob Frankham said:
Thanks Ken

What about if I wanted to deduct 1 month?

I tried DateAdd("m", -1 [myDate]) on a date set to 30.09.03 but it gave me
30.08.03 (31 days is Aug)

Any ideas

Cheers!

Jake
Ken Snell said:
DateAdd("m", 1, [myDate])

--
Ken Snell
<MS ACCESS MVP>

Jacob Frankham said:
Hi

How would I add a month to a date in VBA?

And I don't mean [myDate]+31 because there are not always 31 days in a month

I have 2 textboxes, one defaults to the first of the month, I wish to set
the other to the last day of the same month
(could be 28, 30 days etc)

Cheers

J
Click to expand...
Click to expand...
Click to expand...
 
Hi Ken

Bloody hell, I thought there might have been a simple function (DateMinus)
or something !!!!!!

Anyway, thanks for your time Ken

I need the LAST day of the previous month

so if I have a date 12/11/2003 I would want to see 11/31/2003

Cheers mate

Jake
Ken Snell said:
One month back from 30 September is 30 August, not 31 August.

If you want the last day of the previous month, you could use this
expression:

DateSerial(Year(Date()), Month(Date()), 0)

But that won't give you a "31-day difference" for all dates.

I tried playing with some variations of date calculations, trying to use the
number of days that the current date is from the last day of the current
month, and then applying that to the previous month, but when the two months
have different number of days, the resulting date is too far back or not far
back. I can envision setting up a complicated expression that compares the
number of days in each of the months, and then setting an upper and lower
limit to the one-month back date, but I'm not convinced that it would
work...and you'd need to make a decision about the logic to be used:
namely, do you always count the number of days that the current date is from
the first of the month, or from the last of the month? You can get a
different answer depending upon which direction you go.

If you can identify which date you want as the result (when going back one
month) for the following examples, I probably can come up with an expression
that will work for you:

Start Dates
---------------

12/31/2003

2/24/2003

10/2/2003

3/11/2003

--
Ken Snell
<MS ACCESS MVP>


Jacob Frankham said:
Thanks Ken

What about if I wanted to deduct 1 month?

I tried DateAdd("m", -1 [myDate]) on a date set to 30.09.03 but it gave me
30.08.03 (31 days is Aug)

Any ideas

Cheers!

Jake
Ken Snell said:
DateAdd("m", 1, [myDate])

--
Ken Snell
<MS ACCESS MVP>

Hi

How would I add a month to a date in VBA?

And I don't mean [myDate]+31 because there are not always 31 days in a
month

I have 2 textboxes, one defaults to the first of the month, I wish
Click to expand...
to
set
the other to the last day of the same month
(could be 28, 30 days etc)

Cheers

J
Click to expand...
Click to expand...
Click to expand...
 
The expression that I posted then will do this:

If you want the last day of the month previous to the current date, you
could use this expression:

DateSerial(Year(Date()), Month(Date()), 0)

or, if you want to use a specific date (such as myDate),

DateSerial(Year([myDate()]), Month([myDate]), 0)

--
Ken Snell
<MS ACCESS MVP>


Jacob Frankham said:
Hi Ken

... I thought there might have been a simple function (DateMinus)
or something !!!!!!

Anyway, thanks for your time Ken

I need the LAST day of the previous month

so if I have a date 12/11/2003 I would want to see 11/31/2003

Cheers mate

Jake
Ken Snell said:
One month back from 30 September is 30 August, not 31 August.

If you want the last day of the previous month, you could use this
expression:

DateSerial(Year(Date()), Month(Date()), 0)

But that won't give you a "31-day difference" for all dates.

I tried playing with some variations of date calculations, trying to use the
number of days that the current date is from the last day of the current
month, and then applying that to the previous month, but when the two months
have different number of days, the resulting date is too far back or not far
back. I can envision setting up a complicated expression that compares the
number of days in each of the months, and then setting an upper and lower
limit to the one-month back date, but I'm not convinced that it would
work...and you'd need to make a decision about the logic to be used:
namely, do you always count the number of days that the current date is from
the first of the month, or from the last of the month? You can get a
different answer depending upon which direction you go.

If you can identify which date you want as the result (when going back one
month) for the following examples, I probably can come up with an expression
that will work for you:

Start Dates
---------------

12/31/2003

2/24/2003

10/2/2003

3/11/2003

--
Ken Snell
<MS ACCESS MVP>


Jacob Frankham said:
Thanks Ken

What about if I wanted to deduct 1 month?

I tried DateAdd("m", -1 [myDate]) on a date set to 30.09.03 but it
Click to expand...
Click to expand...
gave
me
30.08.03 (31 days is Aug)

Any ideas

Cheers!

Jake
DateAdd("m", 1, [myDate])

--
Ken Snell
<MS ACCESS MVP>

Hi

How would I add a month to a date in VBA?

And I don't mean [myDate]+31 because there are not always 31 days
Click to expand...
Click to expand...
in
a
Click to expand...
Click to expand...
 
Back
Top