Escalation on form

  • Thread starter Thread starter Douglas J. Steele
  • Start date Start date
D

Douglas J. Steele

It's possible, but you shouldn't store both the Duration and the
ExpirationDate in the table since you can easily calculate one from the
other.

Assuming you store Duration in the table, you can use the DateAdd function
to determine ExpirationDate:

DateAdd("m", [Duration], [DateOfPlacement])
 
Hi there

I have a form for advertising clients, and on that form is a
"DateOfPlacement" field
this is the date that the ads for that client goes out.

then i have a field for "Duration", which is the length of the contract
:6,12,24,36 months - this field is set to "number"

now i have a field called "ExpirationDate", and i want this field to
calculate the date that the contract expires
for eg, if the DateOfPlacement is 01 jan 2008, and the Duration is 12
months, the expiration date field need to state
: 01 jan 2009.

is this possible? and also, is there any way for a report for example to
show all the contract's expiry dates so that we can contact all the
contracts about to expire?

thank you
Melissa
 
Great. thank you.

Another thing i would like to do is have some kind of report that i can
print perhaps every month, to see a list of which client's contracts will
escalate in that month, so that i can send them a letter informing them of
their escalation as well as their "new escalated monthly installment"

what would be the best way to accomplish this?

thank you kindly for all your help

Melissa

Douglas J. Steele said:
It's possible, but you shouldn't store both the Duration and the
ExpirationDate in the table since you can easily calculate one from the
other.

Assuming you store Duration in the table, you can use the DateAdd function
to determine ExpirationDate:

DateAdd("m", [Duration], [DateOfPlacement])

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Melissa said:
Hi there

I have a form for advertising clients, and on that form is a
"DateOfPlacement" field
this is the date that the ads for that client goes out.

then i have a field for "Duration", which is the length of the contract
:6,12,24,36 months - this field is set to "number"

now i have a field called "ExpirationDate", and i want this field to
calculate the date that the contract expires
for eg, if the DateOfPlacement is 01 jan 2008, and the Duration is 12
months, the expiration date field need to state
: 01 jan 2009.

is this possible? and also, is there any way for a report for example to
show all the contract's expiry dates so that we can contact all the
contracts about to expire?

thank you
Melissa
Click to expand...
Click to expand...
 
Create a query that returns those records where the ExpirationDate is in the
current month, and use that query as the RecordSource for the report.

To only get those records that will expire this month, you can use:

WHERE DateAdd("m", [Duration], [DateOfPlacement]) BETWEEN
DateSerial(Year(Date), Month(Date), 1) AND DateSerial(Year(Date),
Month(Date) + 1, 0)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Melissa said:
Great. thank you.

Another thing i would like to do is have some kind of report that i can
print perhaps every month, to see a list of which client's contracts will
escalate in that month, so that i can send them a letter informing them of
their escalation as well as their "new escalated monthly installment"

what would be the best way to accomplish this?

thank you kindly for all your help

Melissa

Douglas J. Steele said:
It's possible, but you shouldn't store both the Duration and the
ExpirationDate in the table since you can easily calculate one from the
other.

Assuming you store Duration in the table, you can use the DateAdd
function to determine ExpirationDate:

DateAdd("m", [Duration], [DateOfPlacement])

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Melissa said:
Hi there

I have a form for advertising clients, and on that form is a
"DateOfPlacement" field
this is the date that the ads for that client goes out.

then i have a field for "Duration", which is the length of the contract
:6,12,24,36 months - this field is set to "number"

now i have a field called "ExpirationDate", and i want this field to
calculate the date that the contract expires
for eg, if the DateOfPlacement is 01 jan 2008, and the Duration is 12
months, the expiration date field need to state
: 01 jan 2009.

is this possible? and also, is there any way for a report for example to
show all the contract's expiry dates so that we can contact all the
contracts about to expire?

thank you
Melissa
Click to expand...
Click to expand...
Click to expand...
 
Back
Top