date fields in form and table

  • Thread starter Thread starter EVOLUE
  • Start date Start date
E

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
 
EVOLUE said:
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?
Click to expand...


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.
 
-----Original Message-----
EVOLUE said:
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?
Click to expand...


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.
Click to expand...
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
 
-----Original Message-----
EVOLUE said:
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?
Click to expand...


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.
Click to expand...
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
 
-----Original Message-----
Marshall said:
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.
.
Click to expand...
Click to expand...
EVOLUE said:
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
Click to expand...


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.
 
Back
Top