RE: Default Value of field based on another field of same record

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi, Naresh.

The way I would handle the default value is to call a private function in
the AfterUpdate event procedure of the date of birth field:

Private Function dteRetire(dteDOB As Date) As Date

Dim dte60 As Date

' Calculate date when employee reaches 60
dte60 = DateValue(DatePart("m", dteDOB) & "/" & DatePart("d", dteDOB) & _
"/" & DatePart("yyyy", dteDOB) + 60)

' Add one month
dteRetire = DateAdd("m", 1, dte60)

' Select the first day of that month
dteRetire = DateValue(DatePart("m", dteRetire) & "/" & 1 & "/" & _
DatePart("yyyy", dteRetire))

' Subtract one day
dteRetire = DateAdd("d", -1, dteRetire)

End Function

Private Sub txtDOB_AfterUpdate()
txtRetire.DefaultValue = "#" & dteRetire([txtDOB]) & "#"
End Sub

Regarding Batch, you do not need to store this value anywhere, simply
display it as a calculated control:

=IIf(IsNull([txtDOB]),"",DatePart("yyyy",[txtDOB]),

or to calculate it from the OfficerID,

=Left(Str([OfficerID]),4) ' if OfficerID is a numeric field
or
=Left([OfficerID],4) ' if it is a Text field

If you need to print Batch for a report, or do groupings on it, calculate it
on the fly in a query.

Hope that helps.
Sprinks

"Naresh Kumar Saini" wrote:

> I am working in MS Access 2002 on Windows 98 SE. I am working on a database
> about employees. It has a single table with following fields:
>
> (1) OfficerID Six digits; YYYY followed by two-digit seniority of that
> year (Primary)
> (2) Name Text
> (3) DOB Date of Birth (I use dd-mm-yyyy format)
> (4) Batch Only year part of a date is considered (e.g. year of birth)
> (5) Retirement Default value to be set to last day of the month when age is
> 60 yrs
>
> The problems are:
>
> (1) Can I set the default value of of 'Retirement' field based on DOB field
> in the same record? I tried setting it in Table design in 'Default Value'
> property of the field and also on the Form. I want to give user a choice
> either to accept the default value of change it. I am stuck somewhere and not
> getting the expected result.
>
> (2) I want to use 'eomonth' function for above to calculate 'Date of
> Retirement' -- eomonth ([DOB],720) -- but the function is not available in
> Access 2002. (Error Message: Undefined unction 'eomonth' in expression.) It
> is available in Access 2000 (on another PC) but is not working eventhough the
> msowcf.dll file is present. I even tried the workaround -- date (year
> ([DOB]), month ([DOB])+721,1)-1 -- but the error message is 'incorrect number
> of arguments'.
>
> (3) Can I, or should I, use date field with format YYYY to store year part
> of date (for (4) Batch field above). Date and Day part is not required to
> maintain. Perhaps it is harmless to keep it has 1st Janaury, but the user
> should see only four digit year.
>
>
> (4) Also, how can I extract the first four digits from the field 'OfficerID'
> (Primary Key) and use it as input (or Default Value) of 'Batch' field in the
> same record. (e.g. if 'OfficeID' is 199723, Batch should be 1997 -- or
> 01-01-1997 in dd-mm-yyyy.
>
> Thanks for any help. I can post the database if required.
>
> Naresh Kumar Saini
> nksaini<at>gmail<dot>com
>
.