Creating ID with numbers and text

[Nefruaten]

Hi guys,

I'm pretty new to Access but I need some help.
I need to create an ID number using first 3 letters of a name followed by 3
numbers that create a unique ID.

Is there a way of doing this in the expression builder?

Many thanks,

 

[Klatuu]

I don't think so. You would have to use some VBA.
In a case like this, it would be better to use two fields; one for the
letters and another for the numbers. You can create an index on the two
fields.

To get the next available number where the 3 letters are the same, you can
do something like this:

Private Function NextIdNumber() As Long
NextIdNumber = Nz(DLookup("[IdNumber]","tblEmployee","[IdInitials] = """
& Me.txtIdInitials & """"),0) + 1
End Sub

 

[John W. Vinson]

Hi guys,

I'm pretty new to Access but I need some help.
I need to create an ID number using first 3 letters of a name followed by 3
numbers that create a unique ID.

Is there a way of doing this in the expression builder?

Many thanks,

Well...

Don't.

This kind of "intelligent key" made sense when folks used filing cabinets.
It's not necessary with a modern relational database! You can use a primary
key such as an autonumber, that the user never needs to even SEE, much less
memorize or enter; and use database tools such as Combo Boxes to select the
record by showing the user human-meaningful information such as the person's
full name, along with other identifying information if needed. The computer
sees the combo's numeric ID field, the person sees the full name, and they're
both happy. Another complication: what if Jane Smith, SMI025, marries and
changes her name to Jane Roberts? Does her ID stay SMI025 or become ROB115 -
and what do you do with all the other tables linked to SMI025, all the
printouts listing SMI025, all the memories in people's heads?

The ONLY reason to build such an ID is to carry on a long-standing, well
entrenched manual system.