Calculations on Dates

[Kelly Lum]

Hello Everyone,

Would appreciate if anyone could advise how to use
expression in access 2000 to do the following:

I have 3 fields

Begin Date (Day,Month Year could be Previous years)
No. Of Years (Will key in the no of years
1,2,3,4,5,6,7,8,9,10)
Expiry Date (Automatically will return the Day,month Year
of Expiry)

Also can it show me the Expiry once I open the access
program automatically base on the expiry date. If daates
that falls on Holidays,Saturdays and Sundays it will show
up on Monday the expiry date.

Anyone can help.Pls do not hesitate contact me if u have
any other questions.

Rgds
Kelly Lum

 

[Al Camp]

Kelly,
Since your capturing BeginDate, and NoOfYears, no need to save
ExpiryDate. It can always be recalculated "on the fly" in any subsequent
report, query, etc...
Use an unbound text control called ExpiryDate, and give it a
ControlSource of...
=DateAdd("yyyy",NZ([NoOfYears]),[BeginDate])
ExpiryDate will always show the correct ExpiryDate... even if you change
BeginDate or NoOfYears.
hth
Al Camp

 

[Guest]

Since it seems the intention is to search by expiry date (or maybe a date
range?), I wonder if it would be more effective to place the formula into a
calculated query field rather than an unbound control. If the idea is that
expiry date is a parameter some variant of the formula will have to be in
either the criteria or a calculated field. If the formula is in the criteria
for the Begin Date field and also in the unbound control, any change to the
formula will need to occur in both places.

Kelly,
Since your capturing BeginDate, and NoOfYears, no need to save
ExpiryDate. It can always be recalculated "on the fly" in any subsequent
report, query, etc...
Use an unbound text control called ExpiryDate, and give it a
ControlSource of...
=DateAdd("yyyy",NZ([NoOfYears]),[BeginDate])
ExpiryDate will always show the correct ExpiryDate... even if you change
BeginDate or NoOfYears.
hth
Al Camp

Hello Everyone,

Would appreciate if anyone could advise how to use
expression in access 2000 to do the following:

I have 3 fields

Begin Date (Day,Month Year could be Previous years)
No. Of Years (Will key in the no of years
1,2,3,4,5,6,7,8,9,10)
Expiry Date (Automatically will return the Day,month Year
of Expiry)

Also can it show me the Expiry once I open the access
program automatically base on the expiry date. If daates
that falls on Holidays,Saturdays and Sundays it will show
up on Monday the expiry date.

Anyone can help.Pls do not hesitate contact me if u have
any other questions.

Rgds
Kelly Lum

 

[Guest]

Maybe Bruce u can show me the way or maybe some form of a examples. Thanks!

Kelly Lum

Since it seems the intention is to search by expiry date (or maybe a date
range?), I wonder if it would be more effective to place the formula into a
calculated query field rather than an unbound control. If the idea is that
expiry date is a parameter some variant of the formula will have to be in
either the criteria or a calculated field. If the formula is in the criteria
for the Begin Date field and also in the unbound control, any change to the
formula will need to occur in both places.

Kelly,
Since your capturing BeginDate, and NoOfYears, no need to save
ExpiryDate. It can always be recalculated "on the fly" in any subsequent
report, query, etc...
Use an unbound text control called ExpiryDate, and give it a
ControlSource of...
=DateAdd("yyyy",NZ([NoOfYears]),[BeginDate])
ExpiryDate will always show the correct ExpiryDate... even if you change
BeginDate or NoOfYears.
hth
Al Camp

Hello Everyone,

Would appreciate if anyone could advise how to use
expression in access 2000 to do the following:

I have 3 fields

Begin Date (Day,Month Year could be Previous years)
No. Of Years (Will key in the no of years
1,2,3,4,5,6,7,8,9,10)
Expiry Date (Automatically will return the Day,month Year
of Expiry)

Also can it show me the Expiry once I open the access
program automatically base on the expiry date. If daates
that falls on Holidays,Saturdays and Sundays it will show
up on Monday the expiry date.

Anyone can help.Pls do not hesitate contact me if u have
any other questions.

Rgds
Kelly Lum