Coding information

[Guest]

I have the following stored in a table, Forname1, Forname2, Surname, DateOf
Birth - this is stored as dd/mm/yyyy, and Sex - stored as male or female, I
need to use the data from these fields to pre populate another field named
drivingLicence.

Here is what i am trying to do.

A B C D
DRIVE / 512185 / YT 9ME

A = First 5 characters of surname. If the surname is less than 5 characters
the figure 9 will be added to the gaps. (e.g. MAN99).

B = First and last numbers are the year of birth. Second and third numbers
are the month of birth. (Note: in the case of female licence holders `5' is
added to the second digit, this means that the second digit will be 5 or 6).
The fourth and fifth digits are the day of the month of your birth.

C = The first two initials of your forenames. If you have only one initial
then the second character will be a `9'.

D = Computer check digits - these will need typing in.

Does anyone know how i can do this so that the drivinglicence field is pre
populated with as much information as possible.

Thanks in advance

Andy

 

[John Smith]

drivingLicence = Left$(Surname & "99999", 5) & "/" _
& (Year(DateOfBirth) - 2000) _
& (Int(Month(DateOfBirth)/10) + iif(Sex="F",5,0)) _
& Format$(Day(DateOfBirth), "00") _
& (Year(DateOfBirth) - 2000) _
& Left$(Nz(Forname1, "9"), 1) _
& Left$(Nz(Forname2, "9"), 1) _
& (Year(DateOfBirth) - 2000)

Not sure what should happen when we reach 2010?

You will need to call this from the AfterUpdate event of each of the controls
involved. For ease of maintenance put the expression in a function and then
call the function from each one so that you don't have four copies of it.

You might also want to consider whether you still want this to happen once the
check digits have been added.

HTH
John