Default Value of field based on another field of same record

[Naresh Kumar Saini]

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

 

[Sprinks]

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

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

 

[Naresh Kumar Saini]

Thanks for detailed help, Sprinks.

I am a new user and can only work through build-in tolls, expression builders.
I tried to use the MacroBuilder in AfterUpdate but I stuck. Now I will try
to work
as you suggested. I am also trying some more ways and it seems there is some
progress. If any success, I will discuss it here.

Meanwhile, how can I make it possible that user type in on Form 1980 and
the date "01-01-1980" (dd-mm-yyyy) is entered in a field in the table. I
will be using only the Year part and day-month will not be used.

Thanks again for help, Sprinks.

Naresh Kumar Saini
nksaini<at>gmail<dot>com

 

[Sprinks]

Hi, Naresh.

As far as using the code I've given you, click on the ellipsis (...) to the
right of the AfterUpdate property field of the DateofBirth field, and choose
Code Builder. The Visual Basic form module window will open, and Access will
create the shell of an AfterUpdate procedure for you. Copy the

txtRetire.DefaultValue = "#" & dteRetire([txtDOB]) & "#"

line between the Sub and End Sub lines, and change "txtRetire" to the name
of your control that has the retire date, and change "txtDOB" to the name of
the DateofBirth control.

Then, anywhere in the module, cut and paste the custom function; it will
work without any changes.

To assign a date of 1/1/####, where the user has typed in the year, assign
the following expression to the control's Control Source:

=IIf(IsNull([YourYearControl]),"",DateValue("1/1/" & [YourYearControl])),

replacing "YourYearControl" with the name of the control in which the user
enters the year.

Hope that helps.
Sprinks

 

[Naresh Kumar Saini]

Thanks again Sprinks

Access provides many ways to build quries, control. Yet help system in
Access is not as extensive as in Excel. I somehow created what I wanted. Here
is my experience:

(1) Default value of a field can be set in Table itself, but it can not be
based on some other field of the same Table. However, default values defined
thus automatically appear in the field and user only have to press Enter to
accept it or just change it.

(2) On Forms, default value of Controls can be set based on field of same
record. In this case value is not visible automatically but user has to press
Ctrl-Shift-Space to enter the default value of Control.

(3) Another way can be to set the After Update property of source Control
and make it to set the default value of target Control. Then, the On Get
Focus property of target Control can be set to a Macro. The Macro can send
the required keystrokes (Ctrl-Shift-Space) to display the default so that
user can see it and accept it or change it. Somehow I could not find the
keystrokes to send. Now I have to manually press Ctrl-Shift-Space to see the
default value.

(4) A new user takes some time to understand the concept behind date
mathematics. Frequently not using correct date syntax and confusing it with
number or text creates problem.

(5) Access is not as portable as other members of MS Office family. A
database created on a machine may not open correctly on other, even if it is
saved in an appropriate version.

(6) Several of in-built functions listed in on-line help are not available
in Expression Builder.

Yet I love Access. I am learning it slowly. I prepared a few simple
databases with single tables and now trying to move ahead.

Thanks Sprinks for help.


Naresh Kumar Saini
nksaini<at>gmail<dot>com

 

[Sprinks]

True enough, Naresh. It is not a perfect application. It is, nevertheless,
quite useful. Good luck in your learning curve. Glad I could help.

Sprinks