Storing the First Letter of First Name, First Letter of Last Name and Data of Birth

[CSCS]

Hi,

I'm designing a database in which the primary key is a combination of the first letter of first name, the first letter of last name and the date of birth.

How can I do that?

Regards,
CS

 

[Bat17]

You will need to do it at form level, but what happens when you end up with a duplicate?
you can use the left() functions to strip out the first letter and the format function on the date to present it the way you want.

Peter

 

[CSCS]

I did that.

I want to store the field with the combined characters in the table.

 

[Bat17]

you will need to use code at form level to add the value to the field for you, but what store it when you create it on the fly when needed.

Peter

 

[CSCS]

What is the code?

 

[Bat17]

I would do it something like this but you should really address the possibility of duplicate combinations

Private Sub LastName_AfterUpdate()
Me.ID = Left(Me.FIRSTNAME, 1) & Left(Me.LASTNAME,1) & Format(Me.dob, "DDMMYYYY")
End Sub

 

[john471]

Hi,

I'm designing a database in which the primary key is a combination of the first letter of first name, the first letter of last name and the date of birth.

How can I do that?

Regards,
CS

Twins : Jeremy and Jason Smith (born on the same day, would you believe ???) just... joined your company or club, or became your customers, or whatever your database pertains to. Your system design prohibits you from putting them both in your database.

As BAT17 has told you (twice), and anyone with any depth of experience in database design would echo :- using this as a Primary Key would be a design flaw.

The good people of this forum give advice like this because they know what peril making these mistakes can cause down the track. Better to "nip it in the bud" ad steer someone down the right track at the earliest opportunity. I urge you to reconsider ignoring this advice.

Regards

John.

 

[CSCS]

I completely understand what you are saying, but my manager is asking for this!!!
I tried to explain what you are saying 100s of itmes to him but that what he wants!

in my form I created an unbound field with the requested combination, now how can I put those in a table field.
in other words, how can I send the content of a form unbound field to a field in the table?

your help is very appreciated.

Regards,
CS.

 

[Bat17]

you don't, you do it as I said by binding the field and poking the value in by code. You will probaly need to run the code from each of the 3 fields in case someone goes back and edits something after.
You also need to make sure that data can only be edited from the form for the same reason.

See if the Boss will accept an Autonumber for the primary key and calculate the number his way on the fly for display.

Peter