Calculating Due Date

[Guest]

I have an Effective_Date field in my table and need to calculate due dates.
If the date is before or equal to the 15th of the month, show the Effective
Date, if after show the first of the next month, that would be the 1st due
date. Then I need to show the second date as 30 days from the first date and
the third date as 60 days from then. The other problem I'm have is in a
report I want to show all of those with a date in say "October" whether it is
the 1st, 2nd or 3rd due date. Can this be done with an expression, or do I
need to create fields for those dates? Thanks for any help.

 

[John W. Vinson]

I have an Effective_Date field in my table and need to calculate due dates.
If the date is before or equal to the 15th of the month, show the Effective
Date, if after show the first of the next month, that would be the 1st due
date. Then I need to show the second date as 30 days from the first date and
the third date as 60 days from then. The other problem I'm have is in a
report I want to show all of those with a date in say "October" whether it is
the 1st, 2nd or 3rd due date. Can this be done with an expression, or do I
need to create fields for those dates? Thanks for any help.

I'm a bit confused. If "the date" is before or equal - today's date, or the
date in the table? You do need to create fields *in the query* - not in your
table - for all these dates.

Guessing a bit here, try three calculated fields in a Query:

FirstDue: IIF(Day(Date()) <= 15, [EffectiveDate], DateSerial(Year(Date()),
Month(Date()) + 1, 1)
SecondDue: DateAdd("d", 30, IIF(Day(Date()) <= 15, [EffectiveDate],
DateSerial(Year(Date()), Month(Date()) + 1, 1))
ThirdDue: DateAdd("d", 60, IIF(Day(Date()) <= 15, [EffectiveDate],
DateSerial(Year(Date()), Month(Date()) + 1, 1))

Put a criterion of

= DateSerial(Year(Date()), Month(Date()), 1) AND < DateSerial(Year(Date()), Month(Date()) + 1, 1)

on each of these three calculated fields, on separate lines in the query grid,
to display all records for which any one of the due dates falls during the
current month.

Replace Day(Date()) by Day([EffectiveDate]) if you mean "the date" to refer to
the effective date.

John W. Vinson [MVP]

 

[pietlinden]

I have an Effective_Date field in my table and need to calculate due dates.
If the date is before or equal to the 15th of the month, show the Effective
Date, if after show the first of the next month, that would be the 1st due
date. Then I need to show the second date as 30 days from the first date and
the third date as 60 days from then. The other problem I'm have is in a
report I want to show all of those with a date in say "October" whether it is
the 1st, 2nd or 3rd due date. Can this be done with an expression, or do I
need to create fields for those dates? Thanks for any help.

use a query.
use IIF and day(date) to get the number of the day of the month.
then use DateAdd to add 30 days
use a filter when you open your report something like
DueDate BETWEEN #10/1/2007# AND #10/31/2007#

You might be better off prompting the user for the date range with an
unbound form and then calculating the last day of the month... Use
something like dateadd and subtract a day from the first of the next
month.

 

[Guest]

This was very helpful, and yes, I did want to calculate it on EffectiveDate.
However, for those with a date past the 15th of the month, it calculated the
FirstDue correctly, but the 2nd date jumps to 12/1/07, it didn't matter which
month it was, and then all the 3rd Due says 12/31/07. But this does help
alot. Thanks

I have an Effective_Date field in my table and need to calculate due dates.
If the date is before or equal to the 15th of the month, show the Effective
Date, if after show the first of the next month, that would be the 1st due
date. Then I need to show the second date as 30 days from the first date and
the third date as 60 days from then. The other problem I'm have is in a
report I want to show all of those with a date in say "October" whether it is
the 1st, 2nd or 3rd due date. Can this be done with an expression, or do I
need to create fields for those dates? Thanks for any help.

I'm a bit confused. If "the date" is before or equal - today's date, or the
date in the table? You do need to create fields *in the query* - not in your
table - for all these dates.

Guessing a bit here, try three calculated fields in a Query:

FirstDue: IIF(Day(Date()) <= 15, [EffectiveDate], DateSerial(Year(Date()),
Month(Date()) + 1, 1)
SecondDue: DateAdd("d", 30, IIF(Day(Date()) <= 15, [EffectiveDate],
DateSerial(Year(Date()), Month(Date()) + 1, 1))
ThirdDue: DateAdd("d", 60, IIF(Day(Date()) <= 15, [EffectiveDate],
DateSerial(Year(Date()), Month(Date()) + 1, 1))

Put a criterion of

= DateSerial(Year(Date()), Month(Date()), 1) AND < DateSerial(Year(Date()), Month(Date()) + 1, 1)

on each of these three calculated fields, on separate lines in the query grid,
to display all records for which any one of the due dates falls during the
current month.

Replace Day(Date()) by Day([EffectiveDate]) if you mean "the date" to refer to
the effective date.

John W. Vinson [MVP]

 

[John W. Vinson]

This was very helpful, and yes, I did want to calculate it on EffectiveDate.
However, for those with a date past the 15th of the month, it calculated the
FirstDue correctly, but the 2nd date jumps to 12/1/07, it didn't matter which
month it was, and then all the 3rd Due says 12/31/07. But this does help
alot. Thanks

Are you using something like

FirstDue: IIF(Day([EffectiveDate]) <= 15, [EffectiveDate],
DateSerial(Year([EffectiveDate]),
Month([EffectiveDate]) + 1, 1)

and similarly for the others? If so and it's still not working, please post
the code you're actually using.

John W. Vinson [MVP]

 

[Guest]

It worked! I was leaving out the field name in one of the statements. It
works great. Thanks for all your help

This was very helpful, and yes, I did want to calculate it on EffectiveDate.
However, for those with a date past the 15th of the month, it calculated the
FirstDue correctly, but the 2nd date jumps to 12/1/07, it didn't matter which
month it was, and then all the 3rd Due says 12/31/07. But this does help
alot. Thanks

Are you using something like

FirstDue: IIF(Day([EffectiveDate]) <= 15, [EffectiveDate],
DateSerial(Year([EffectiveDate]),
Month([EffectiveDate]) + 1, 1)

and similarly for the others? If so and it's still not working, please post
the code you're actually using.

John W. Vinson [MVP]