Default Value of field based on another field of same record

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

.

Relevant Pages

  • Re: How should I generate a primary key?
    ... the external reality and verify them. ... be verified for syntax or check digits inside itself. ... A surrogate key is system generated to replace the actual key behind ... with a quote from Dr. Codd: "..Database users ...
    (comp.databases)
  • Re: Critique of Robert C. Martins "Agile Principles, Patterns, and Practices"
    ... foreach e in retireableEmployees ... Why ware we sending this particular set of employees the retirement options letter? ... Because we can run the tests on our local laptops without a database. ... I agree that relations are the only needed data structures. ...
    (comp.object)
  • Re: encryption with python
    ... > and a birth date (8 digits, could be padded to be 9) and obtain a new ... quick-and-dirty student ID algorithm: ... I'd check the database to see if the number has ... an attacker might be able to work out roughly what ...
    (comp.lang.python)
  • Re: Parts Numbering Scheme
    ... >numbering scheme. ... The next two digits indicate a major subsystem or group of related parts ... A big reason why local phone numbers in the US are ... I would create a database to go with this, with the part number as the ...
    (sci.electronics.design)
  • Re: Larger field than thought?
    ... then let the database grab the ... Format it to 3 digits, and bam!....I have a new part number. ... reports or display, but I created the database this way ... I thought that was the Update Query I would be looking at. ...
    (microsoft.public.access.tablesdbdesign)