date fields in form and table

[EVOLUE]

Dear all,

I am working on a database to keep members' records. One
of the field in the table (as well as the form i created
for entering data) is Enrollment Date which i had set to
a medium date format.

Is it possible to set another field (Membership Expiry
Date) in the same table automatically, each time a new
record is entered, to a year after the Enrollment Date?

Or would it be easier to have 3 separate fields for this
Enrollment Date (ie 1 field each for day, month and year)
so that i will only need to add 1 to the year field? Is
it possible that my form has these 3 fields as combo
boxes and upon entering new record, they can be combined
into the single field in the table?

Will appreciate any help.. thanks in advance!

regards,
eunice

 

[Marshall Barton]

I am working on a database to keep members' records. One
of the field in the table (as well as the form i created
for entering data) is Enrollment Date which i had set to
a medium date format.

Is it possible to set another field (Membership Expiry
Date) in the same table automatically, each time a new
record is entered, to a year after the Enrollment Date?

Or would it be easier to have 3 separate fields for this
Enrollment Date (ie 1 field each for day, month and year)
so that i will only need to add 1 to the year field? Is
it possible that my form has these 3 fields as combo
boxes and upon entering new record, they can be combined
into the single field in the table?

There is no need to break the date into multiple fields.
You can easily add a year to a date using the DateAdd
function:

DateAdd("yyyy", 1, [Enrollment Date])

You can display the expiry date by using that in a text box
on the form or in a report. However, if the expriy date is
always one year after the enrollment date, you should not
store it in the table. Instead, you should use the above
expression whenever you need to display it.

 

[EVOLUE]

-----Original Message-----

I am working on a database to keep members' records. One
of the field in the table (as well as the form i created
for entering data) is Enrollment Date which i had set to
a medium date format.

Is it possible to set another field (Membership Expiry
Date) in the same table automatically, each time a new
record is entered, to a year after the Enrollment Date?

Or would it be easier to have 3 separate fields for this
Enrollment Date (ie 1 field each for day, month and year)
so that i will only need to add 1 to the year field? Is
it possible that my form has these 3 fields as combo
boxes and upon entering new record, they can be combined
into the single field in the table?

There is no need to break the date into multiple fields.
You can easily add a year to a date using the DateAdd
function:

DateAdd("yyyy", 1, [Enrollment Date])

You can display the expiry date by using that in a text box
on the form or in a report. However, if the expriy date is
always one year after the enrollment date, you should not
store it in the table. Instead, you should use the above
expression whenever you need to display it.

Hi Marsh,

Thanks for the help =)
Just need to confirm that the DateAdd function is placed
in Default Value of the Expiry Date field in the table?
and it will be set automatically with each new entry?

ps: the expiry date does not stay at a year after
enrollment but changes constantly as and when membership
is renewed

really appreciate the help!

regards, eunice

 

[EVOLUE]

-----Original Message-----

I am working on a database to keep members' records. One
of the field in the table (as well as the form i created
for entering data) is Enrollment Date which i had set to
a medium date format.

Is it possible to set another field (Membership Expiry
Date) in the same table automatically, each time a new
record is entered, to a year after the Enrollment Date?

Or would it be easier to have 3 separate fields for this
Enrollment Date (ie 1 field each for day, month and year)
so that i will only need to add 1 to the year field? Is
it possible that my form has these 3 fields as combo
boxes and upon entering new record, they can be combined
into the single field in the table?

There is no need to break the date into multiple fields.
You can easily add a year to a date using the DateAdd
function:

DateAdd("yyyy", 1, [Enrollment Date])

You can display the expiry date by using that in a text box
on the form or in a report. However, if the expriy date is
always one year after the enrollment date, you should not
store it in the table. Instead, you should use the above
expression whenever you need to display it.

Hi Marsh,

Thanks for the help =)
Just need to confirm that the DateAdd function is placed
in Default Value of the Expiry Date field in the table?
and it will be set automatically with each new entry?

ps: the expiry date does not stay at a year after
enrollment but changes constantly as and when membership
is renewed

really appreciate the help!

regards, eunice

 

[Marshall Barton]

-----Original Message-----

There is no need to break the date into multiple fields.
You can easily add a year to a date using the DateAdd
function:

DateAdd("yyyy", 1, [Enrollment Date])

You can display the expiry date by using that in a text box
on the form or in a report. However, if the expriy date is
always one year after the enrollment date, you should not
store it in the table. Instead, you should use the above
expression whenever you need to display it.
.

Just need to confirm that the DateAdd function is placed
in Default Value of the Expiry Date field in the table?
and it will be set automatically with each new entry?

ps: the expiry date does not stay at a year after
enrollment but changes constantly as and when membership
is renewed

No, you can not have a default value that depends on another
field.

If you allow users to edit the expiry date, then you will
probably want to set it using code in the enrollment date
control's AfterUpdate event:

If IsNull(Me.txtExpiryDate) Then 'skip if already set
Me.txtExpiryDate = DateAdd("yyyy", 1,Me.txtEnrollmentDate)
End If

But I'm not at all clear on who, when of how the expiry date
is allowed to be modified once it has been set.