Date Math / Manipulation

[Guest]

We are logging in different types of media that comes from our clients on
regular monthly intervals. I have two fields within a table, one is a date
field which is an expected date the media is due to arrive. I have a second
field that is a number field which contains a number from 1 – 31. This field
is the day of the month that the media is due each month Ex: 10 – every month
the media is due on the 10th. Every client has different days. I have a
button that is clicked to log in the media. Here is what I want to do. In
this click event, I want to update the expected date field by adding a month,
but I need to over ride the day with the value from the number field. Ex:
Number field = 15, I log in the media today, update expected date for next
month, but not one month from today, next month with a date of the 15th. I
could use some suggestions.
Thanks

 

[Jeff Boyce]

Kenny

Take a look at the DateSerial() function, and an Update query (by the way,
if the "next" due date is always calculated the same way, you DON'T need to
store it in your table). You could simply do the calculation in a query to
see the next due date, and avoid all the extra synchronization code work
you'd need to do if you did store a calculated value.

Your expression might look something like:

DateSerial(Year(Date()),Month(Date())+1,[YourExpectedDay])

Note that this could produce a bogus date (with accompanying Access error)
if today's date (Date()) were the 20th of January, and your [ExpectedDate]
was 31 -- February doesn't have that many days...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Hi Jeff,
Thanks for replying. The next due date is different for each client, so
it is not the same number for every client, ex: one client's media is due on
the 5th one is due on the 21st one is due on the 15th, etc... I need to set
the updated date expected to the following month, but for that client's given
evpected day of the month. I use the expected date for a report that I run
based on DateDiff of current date and the expected date for overdue media.
Whenever the media may arrive, I log it in with current date, but now want to
set next month's expected date for that client's specific day of the month.
Thanks

Kenny

Take a look at the DateSerial() function, and an Update query (by the way,
if the "next" due date is always calculated the same way, you DON'T need to
store it in your table). You could simply do the calculation in a query to
see the next due date, and avoid all the extra synchronization code work
you'd need to do if you did store a calculated value.

Your expression might look something like:

DateSerial(Year(Date()),Month(Date())+1,[YourExpectedDay])

Note that this could produce a bogus date (with accompanying Access error)
if today's date (Date()) were the 20th of January, and your [ExpectedDate]
was 31 -- February doesn't have that many days...

Regards

Jeff Boyce
Microsoft Office/Access MVP

We are logging in different types of media that comes from our clients on
regular monthly intervals. I have two fields within a table, one is a
date
field which is an expected date the media is due to arrive. I have a
second
field that is a number field which contains a number from 1 - 31. This
field
is the day of the month that the media is due each month Ex: 10 - every
month
the media is due on the 10th. Every client has different days. I have a
button that is clicked to log in the media. Here is what I want to do. In
this click event, I want to update the expected date field by adding a
month,
but I need to over ride the day with the value from the number field.
Ex:
Number field = 15, I log in the media today, update expected date for next
month, but not one month from today, next month with a date of the 15th.
I
could use some suggestions.
Thanks

 

[Jeff Boyce]

Kenny

I didn't do a good enough job of explaining.

If each client has a "DueOnDay" value, you can create a query using the
DateSerial() function to calculate one (or more) client's new due date
(using each client's "DueOnDay" value).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff,
Thanks for replying. The next due date is different for each client, so
it is not the same number for every client, ex: one client's media is due
on
the 5th one is due on the 21st one is due on the 15th, etc... I need to
set
the updated date expected to the following month, but for that client's
given
evpected day of the month. I use the expected date for a report that I run
based on DateDiff of current date and the expected date for overdue
media.
Whenever the media may arrive, I log it in with current date, but now want
to
set next month's expected date for that client's specific day of the
month.
Thanks

Kenny

Take a look at the DateSerial() function, and an Update query (by the
way,
if the "next" due date is always calculated the same way, you DON'T need
to
store it in your table). You could simply do the calculation in a query
to
see the next due date, and avoid all the extra synchronization code work
you'd need to do if you did store a calculated value.

Your expression might look something like:

DateSerial(Year(Date()),Month(Date())+1,[YourExpectedDay])

Note that this could produce a bogus date (with accompanying Access
error)
if today's date (Date()) were the 20th of January, and your
[ExpectedDate]
was 31 -- February doesn't have that many days...

Regards

Jeff Boyce
Microsoft Office/Access MVP

We are logging in different types of media that comes from our clients
on
regular monthly intervals. I have two fields within a table, one is a
date
field which is an expected date the media is due to arrive. I have a
second
field that is a number field which contains a number from 1 - 31. This
field
is the day of the month that the media is due each month Ex: 10 - every
month
the media is due on the 10th. Every client has different days. I have a
button that is clicked to log in the media. Here is what I want to do.
In
this click event, I want to update the expected date field by adding a
month,
but I need to over ride the day with the value from the number field.
Ex:
Number field = 15, I log in the media today, update expected date for
next
month, but not one month from today, next month with a date of the
15th.
I
could use some suggestions.
Thanks

 

[James A. Fortune]

Kenny

Take a look at the DateSerial() function, and an Update query (by the way,
if the "next" due date is always calculated the same way, you DON'T need to
store it in your table). You could simply do the calculation in a query to
see the next due date, and avoid all the extra synchronization code work
you'd need to do if you did store a calculated value.

Your expression might look something like:

DateSerial(Year(Date()),Month(Date())+1,[YourExpectedDay])

Note that this could produce a bogus date (with accompanying Access error)
if today's date (Date()) were the 20th of January, and your [ExpectedDate]
was 31 -- February doesn't have that many days...

Regards

Jeff Boyce
Microsoft Office/Access MVP

The DateSerial function won't be kind enough to let you know that
there's a problem. For example, it won't error on
DateSerial(Year(#1/20/06#), Month(#1/20/06#) + 1, 31). Instead, it
produces #3/3/2006#. Your warning is still valid.

James A. Fortune
(e-mail address removed)

Still glides the Stream, and shall for ever glide; The Form remains, the
Function never dies. -- William Wordsworth

 

[Guest]

I see the problem with the 31st going from Jan to Feb ending up pushing into
March. Is there a way (and why doesn't Access) compensate for this so would
become Feb 28th (or 29th leap year) or for that matter any time have a 31st
that goes to following month with 30 days would become the 30th?

Jeff, I'm sorry to say this, but I still do not understand your explanation.
Each client has an expected due date, but it is not the same for each client.
One may be due on the 1st of the month, one may be due on the 19th, one on
the 5th, one on the 28th, one on the 15th etc... So a set formula I do not
see working. It's not just a month from the current day (date logged in).
Like I said in my earlier posting, I run a report showing overdue media.
Let's say a client's media is due on the first of every month and so far has
not arrived (using Oct 4th). When I run my report, it will show me client
XYZ's media is 3 days late. Now if it comes in later today and I log it in,
the next expected date is Nov 1st even though I logged it in today. So it is
not a month from today, but set to the following month with the client's
expected day. I hope this is more clarifying of my objective. Thanks again
for your and James’s help

Kenny

Take a look at the DateSerial() function, and an Update query (by the way,
if the "next" due date is always calculated the same way, you DON'T need to
store it in your table). You could simply do the calculation in a query to
see the next due date, and avoid all the extra synchronization code work
you'd need to do if you did store a calculated value.

Your expression might look something like:

DateSerial(Year(Date()),Month(Date())+1,[YourExpectedDay])

Note that this could produce a bogus date (with accompanying Access error)
if today's date (Date()) were the 20th of January, and your [ExpectedDate]
was 31 -- February doesn't have that many days...

Regards

Jeff Boyce
Microsoft Office/Access MVP

The DateSerial function won't be kind enough to let you know that
there's a problem. For example, it won't error on
DateSerial(Year(#1/20/06#), Month(#1/20/06#) + 1, 31). Instead, it
produces #3/3/2006#. Your warning is still valid.

James A. Fortune
(e-mail address removed)

Still glides the Stream, and shall for ever glide; The Form remains, the
Function never dies. -- William Wordsworth

 

[Jeff Boyce]

The "set formula" includes a variable ... the "day-of-month" value you said
you store.

CustomerA has a "day-of-month" value of 1, CustomerB has "15", CustomerC has
"28", ...

If you query the table(s) for CustomerA and his/her "day-of-month" value,
and use the DateSerial() function as I described (not literally, but as a
model, your syntax may vary), you'd be finding the Month+1, "Day-of-Month"
value for CustomerA.

If you did this for all customers, each customer's calculated "next due"
would use each customer's "day-of-month" value to do the calculation.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I see the problem with the 31st going from Jan to Feb ending up pushing
into
March. Is there a way (and why doesn't Access) compensate for this so
would
become Feb 28th (or 29th leap year) or for that matter any time have a
31st
that goes to following month with 30 days would become the 30th?

Jeff, I'm sorry to say this, but I still do not understand your
explanation.
Each client has an expected due date, but it is not the same for each
client.
One may be due on the 1st of the month, one may be due on the 19th, one on
the 5th, one on the 28th, one on the 15th etc... So a set formula I do not
see working. It's not just a month from the current day (date logged in).
Like I said in my earlier posting, I run a report showing overdue media.
Let's say a client's media is due on the first of every month and so far
has
not arrived (using Oct 4th). When I run my report, it will show me client
XYZ's media is 3 days late. Now if it comes in later today and I log it
in,
the next expected date is Nov 1st even though I logged it in today. So it
is
not a month from today, but set to the following month with the client's
expected day. I hope this is more clarifying of my objective. Thanks
again
for your and James's help

Kenny

Take a look at the DateSerial() function, and an Update query (by the
way,
if the "next" due date is always calculated the same way, you DON'T
need to
store it in your table). You could simply do the calculation in a
query to
see the next due date, and avoid all the extra synchronization code
work
you'd need to do if you did store a calculated value.

Your expression might look something like:

DateSerial(Year(Date()),Month(Date())+1,[YourExpectedDay])

Note that this could produce a bogus date (with accompanying Access
error)
if today's date (Date()) were the 20th of January, and your
[ExpectedDate]
was 31 -- February doesn't have that many days...

Regards

Jeff Boyce
Microsoft Office/Access MVP

The DateSerial function won't be kind enough to let you know that
there's a problem. For example, it won't error on
DateSerial(Year(#1/20/06#), Month(#1/20/06#) + 1, 31). Instead, it
produces #3/3/2006#. Your warning is still valid.

James A. Fortune
(e-mail address removed)

Still glides the Stream, and shall for ever glide; The Form remains, the
Function never dies. -- William Wordsworth

 

[Guest]

Thanks Jeff, I understand now. Any thoughts to the second part I noted
about the how Access does not compensate for the date not adjusting for 31st
going into 28th or 30th? Thanks again for your help

The "set formula" includes a variable ... the "day-of-month" value you said
you store.

CustomerA has a "day-of-month" value of 1, CustomerB has "15", CustomerC has
"28", ...

If you query the table(s) for CustomerA and his/her "day-of-month" value,
and use the DateSerial() function as I described (not literally, but as a
model, your syntax may vary), you'd be finding the Month+1, "Day-of-Month"
value for CustomerA.

If you did this for all customers, each customer's calculated "next due"
would use each customer's "day-of-month" value to do the calculation.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I see the problem with the 31st going from Jan to Feb ending up pushing
into
March. Is there a way (and why doesn't Access) compensate for this so
would
become Feb 28th (or 29th leap year) or for that matter any time have a
31st
that goes to following month with 30 days would become the 30th?

Jeff, I'm sorry to say this, but I still do not understand your
explanation.
Each client has an expected due date, but it is not the same for each
client.
One may be due on the 1st of the month, one may be due on the 19th, one on
the 5th, one on the 28th, one on the 15th etc... So a set formula I do not
see working. It's not just a month from the current day (date logged in).
Like I said in my earlier posting, I run a report showing overdue media.
Let's say a client's media is due on the first of every month and so far
has
not arrived (using Oct 4th). When I run my report, it will show me client
XYZ's media is 3 days late. Now if it comes in later today and I log it
in,
the next expected date is Nov 1st even though I logged it in today. So it
is
not a month from today, but set to the following month with the client's
expected day. I hope this is more clarifying of my objective. Thanks
again
for your and James's help

Jeff Boyce wrote:
Kenny

Take a look at the DateSerial() function, and an Update query (by the
way,
if the "next" due date is always calculated the same way, you DON'T
need to
store it in your table). You could simply do the calculation in a
query to
see the next due date, and avoid all the extra synchronization code
work
you'd need to do if you did store a calculated value.

Your expression might look something like:

DateSerial(Year(Date()),Month(Date())+1,[YourExpectedDay])

Note that this could produce a bogus date (with accompanying Access
error)
if today's date (Date()) were the 20th of January, and your
[ExpectedDate]
was 31 -- February doesn't have that many days...

Regards

Jeff Boyce
Microsoft Office/Access MVP

The DateSerial function won't be kind enough to let you know that
there's a problem. For example, it won't error on
DateSerial(Year(#1/20/06#), Month(#1/20/06#) + 1, 31). Instead, it
produces #3/3/2006#. Your warning is still valid.

James A. Fortune
(e-mail address removed)

Still glides the Stream, and shall for ever glide; The Form remains, the
Function never dies. -- William Wordsworth

 

[James A. Fortune]

I see the problem with the 31st going from Jan to Feb ending up pushing into
March. Is there a way (and why doesn't Access) compensate for this so would
become Feb 28th (or 29th leap year) or for that matter any time have a 31st
that goes to following month with 30 days would become the 30th?

I'll take a guess at a reasonable behavior. If the date exists in the
next month, use it, otherwise use the last day of the next month. The
DateSerial function automatically handles leap years. My guess is that
the behavior when the day exceeds the number of days in the month is to
maximize the potential uses of the DateSerial function.

Something like:

Me!txtNextDueDate.Value = IIf(Day(Date()) =
Day(DateSerial(Year(Date()),Month(Date()) + 1,Me!txtDueDay.Value)),
DateSerial(Year(Date()),Month(Date()) + 1,Me!txtDueDay.Value),
DateSerial(Year(Date()),Month(Date()) + 2,0))

This is one of the other uses of the DateSerial function. It converts a
month value of 13 into month 1 of the following year. January 29th,
30th and 31st will return the last day in February, even in leap years.
The 31st of any month will return the last day in the following month,
even December 31st. The IsDate function is another way to test for a
valid date. Make sure the date entered is valid before using this
expression.

Note that DateAdd("m", 1, dtValid) behaves the way you desire but
involves similar checks with the DueDay value so it's not much better.
I hope this helps you get started.

James A. Fortune
(e-mail address removed)

 

[Jeff Boyce]

Kenny

I'd probably just use a "brute force" method, if this were mine. If you
never use a day-of-month after the 28th, you never have to worry about the
"due date" rolling into the next month!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jeff, I understand now. Any thoughts to the second part I noted
about the how Access does not compensate for the date not adjusting for
31st
going into 28th or 30th? Thanks again for your help

The "set formula" includes a variable ... the "day-of-month" value you
said
you store.

CustomerA has a "day-of-month" value of 1, CustomerB has "15", CustomerC
has
"28", ...

If you query the table(s) for CustomerA and his/her "day-of-month" value,
and use the DateSerial() function as I described (not literally, but as a
model, your syntax may vary), you'd be finding the Month+1,
"Day-of-Month"
value for CustomerA.

If you did this for all customers, each customer's calculated "next due"
would use each customer's "day-of-month" value to do the calculation.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I see the problem with the 31st going from Jan to Feb ending up pushing
into
March. Is there a way (and why doesn't Access) compensate for this so
would
become Feb 28th (or 29th leap year) or for that matter any time have a
31st
that goes to following month with 30 days would become the 30th?

Jeff, I'm sorry to say this, but I still do not understand your
explanation.
Each client has an expected due date, but it is not the same for each
client.
One may be due on the 1st of the month, one may be due on the 19th, one
on
the 5th, one on the 28th, one on the 15th etc... So a set formula I do
not
see working. It's not just a month from the current day (date logged
in).
Like I said in my earlier posting, I run a report showing overdue
media.
Let's say a client's media is due on the first of every month and so
far
has
not arrived (using Oct 4th). When I run my report, it will show me
client
XYZ's media is 3 days late. Now if it comes in later today and I log it
in,
the next expected date is Nov 1st even though I logged it in today. So
it
is
not a month from today, but set to the following month with the
client's
expected day. I hope this is more clarifying of my objective. Thanks
again
for your and James's help

:

Jeff Boyce wrote:
Kenny

Take a look at the DateSerial() function, and an Update query (by
the
way,
if the "next" due date is always calculated the same way, you DON'T
need to
store it in your table). You could simply do the calculation in a
query to
see the next due date, and avoid all the extra synchronization code
work
you'd need to do if you did store a calculated value.

Your expression might look something like:

DateSerial(Year(Date()),Month(Date())+1,[YourExpectedDay])

Note that this could produce a bogus date (with accompanying Access
error)
if today's date (Date()) were the 20th of January, and your
[ExpectedDate]
was 31 -- February doesn't have that many days...

Regards

Jeff Boyce
Microsoft Office/Access MVP

The DateSerial function won't be kind enough to let you know that
there's a problem. For example, it won't error on
DateSerial(Year(#1/20/06#), Month(#1/20/06#) + 1, 31). Instead, it
produces #3/3/2006#. Your warning is still valid.

James A. Fortune
(e-mail address removed)

Still glides the Stream, and shall for ever glide; The Form remains,
the
Function never dies. -- William Wordsworth