How do I do nested (3-4) functions--step by step?

[frostroom102]

I am a db newbie and don't know how to do nested functions for a problem I
have:

Calculate a 6-digit Employee Code for each employee using a complex
concatenation formula. The components fo the formula are as follows:

1. The first 2 digits fo the Employee Code are the last two letters of the
Last name in uppercase.

2. Calculate the second 2 digits of the Employee Code by multiplying the Day
extracted from the Birthdate, by 0.67. Truncate any decimal positions. Do not
round

3. The last two digits of the Employee Code are calculated by finding the
length of the Last field and multiplying it by 2.75. Truncate any decimal
positions.

Here is the first two lines of the spreadsheet:

First Last Birthdate Employee Code Age
Salary
Samuel Adams 7/23/1951 ? ?
62,300

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Spreadsheet? Is this an Excel s/s? Since you posted in an Access
Queries newsgroup I'll assume you just "mis-typed" and meant table
instead of spreadsheet.

In that case here is a formula in VBA code, that can also be used in a
query, that will get the results you want:

SELECT UCase(Right(Trim([Last]),2)) &
Int(Day(Birthdate) * 0.67) &
Int(Len(Trim([Last])) * 2.75) As EmployeeCode

FROM table_name
WHERE ...

If each segment of the code needs to have 2 chars then the formula needs
to look like this:

UCase(Right(Trim([Last]), 2)) &
Format(Int(Day(Birthdate) * 0.67), "00") &
Format(Int(Len(Trim([Last])) * 2.75), "00")

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlxRVoechKqOuFEgEQIY6ACg7mEgBryYkUhgTkZsYWEuMMm9aIMAoKb9
OvrbqkZnAqnvQhTHFeT2A6D+
=33U2
-----END PGP SIGNATURE-----