Field Date Default value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi and Thanks for your help.

I have two attributes in a table: Date_Action_Reminder and
Date_Action_Required.

Users will enter a date, for example 08/01/2006, in the Date_Action_Required
field. I want the Date_Action_Reminder to default to 07/28/2006. Exactly five
days prior to the Action_Date_Required.

Can I program this in the table, if so, how?
 
You can not program this at table level, nor should you.

One of the rules of a relational database is that you should not store
derived data. Since you have a Date_Action_Required field, you can always
calculate the Date_Action_Reminder in queries, forms, and reports by just
adding 5 days to the Date_Action_Required.

If that sounds like a lot of extra work consider:

1. Having derived data takes up space in the tables.

2. Derived data tends to result in inconsistant data. For example I have an
Employee table that includes both the Date of Birth and Age. After a year or
so, all the data in the Age field is incorrect.

3. Increase workload. If you change the Date_Action_Required data, then you
need to also change the Date_Action_Reminder data.
 
Hi and Thanks for your help.

I have two attributes in a table: Date_Action_Reminder and
Date_Action_Required.

Users will enter a date, for example 08/01/2006, in the Date_Action_Required
field. I want the Date_Action_Reminder to default to 07/28/2006. Exactly five
days prior to the Action_Date_Required.

Can I program this in the table, if so, how?
Click to expand...

No, you cannot. Table Default values cannot reference any other field,
nor any user functions.

You can do it on a Form with a bit of VBA code, but... is it even
necessary for the field to exist? You can easily calculate
DateAdd("d", -5, [Date_Action_Required]) in a Query and use that
calculated date for searching, sorting, filtering a form, etc.; it
will always be exactly five days prior to the required date.

If it *is* truly a default, meaning that five days is the usual span
but that you want it allowable to edit the date, then use VBA code in
the AfterUpdate of the form control bound to Date_Action_Required
(which I'll assume that you have named txtDate_Action_Required):

Private Sub txtDate_Action_Required_AfterUpdate()
If IsNull(Me!txtDate_Action_Reminder) Then ' don't stomp on existing
Me!txtDate_Action_Reminder = DateAdd("d", 5, _
Me!txtDate_Action_Required)
End If
End Sub

John W. Vinson[MVP]
 
Hello Gus.

Gus said:
I have two attributes in a table: Date_Action_Reminder and
Date_Action_Required.

Users will enter a date, for example 08/01/2006, in the
Date_Action_Required field. I want the Date_Action_Reminder
to default to 07/28/2006.
Exactly five days prior to the Action_Date_Required.

Can I program this in the table, if so, how?
Click to expand...

Sorry, you can't program this in a table.
The default values are applied only when the new record is created, that is
before the user enters data.
[btw: five days prior to 08/01/2006 is 07/27/2006.]
In a form, you could use the AfterUpdate event of the textbox for the
Action_Date_Required to change the value of the Date_Action_Reminder
field, for example like this:

Private Sub Action_Date_Required_AfterUpdate()
Me.Date_Action_Reminder = _
DateAdd("d", -5, Me.Action_Date_Required)
End Sub

If the user wasn't intended to change the Date_Action_Reminder field,
you could calculate the value whenever you need to from the other field
and therfore should not store it in the table.
 
Thank you Jerry!

Great answer and also appreciate your explanation. Helps to increase
knowledge base.
 
Thanks John. I was wondering if I could somehow make the five the set
difference, but allow to override if needed. I will follow your example.

John Vinson said:
Hi and Thanks for your help.

I have two attributes in a table: Date_Action_Reminder and
Date_Action_Required.

Users will enter a date, for example 08/01/2006, in the Date_Action_Required
field. I want the Date_Action_Reminder to default to 07/28/2006. Exactly five
days prior to the Action_Date_Required.

Can I program this in the table, if so, how?
Click to expand...

No, you cannot. Table Default values cannot reference any other field,
nor any user functions.

You can do it on a Form with a bit of VBA code, but... is it even
necessary for the field to exist? You can easily calculate
DateAdd("d", -5, [Date_Action_Required]) in a Query and use that
calculated date for searching, sorting, filtering a form, etc.; it
will always be exactly five days prior to the required date.

If it *is* truly a default, meaning that five days is the usual span
but that you want it allowable to edit the date, then use VBA code in
the AfterUpdate of the form control bound to Date_Action_Required
(which I'll assume that you have named txtDate_Action_Required):

Private Sub txtDate_Action_Required_AfterUpdate()
If IsNull(Me!txtDate_Action_Reminder) Then ' don't stomp on existing
Me!txtDate_Action_Reminder = DateAdd("d", 5, _
Me!txtDate_Action_Required)
End If
End Sub

John W. Vinson[MVP]
Click to expand...
 
Back
Top