Partial part of field to make new field

[Guest]

Hi

I am developing a system whereby I need the system to generate a customer ID
to be displayed in the following format:

L - Surname Initial
999 - General Autonumber code

The field that this info comes from is Customer table - [Last Name]

Anyone have any ideas??

Thanks in advance

 

[Jeff Boyce]

Use a query. Concatenate the values from your [LastName] field and your
[ID] field.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Guest]

I encountered a similar issue. Mine was solved with the creation of an
additional table which has the sole purpose of generating the ID numbers.

I would recommend fields in the new table were:
RecordID (PK)
SurnameID
BaseID
CustID
AltRecordID (FK)

I based my form on a query of my primary table and the new table, with the
relationship of the RecordID on the main table to the AltRecordID on the new
table.

I then referenced the fields on my form as follows:

=====================================================
If IsNull(Forms![frm_Sig19]![S19_CCNo]) Then
Forms![frm_Sig19]![AttachedRCN] = Forms![frm_Sig19]![S19_RCN]
Me.YearID = Year(Forms![frm_Sig19]![S19_TSD])
Me.BaseID = 1 + DCount("[BaseID]", "[tbl_CCNo]", _
"[YearID]='" & Forms![frm_Sig19]![YearID] & "'")
End If
Me.CCNo = Me.YearID & "-" & Me.BaseID
=====================================================
Replace my form names and form fields with your corresponding names.
YearID would equal your surname, and would be as simple as Left([Surname],1)
BaseID is same, and counts your records which match your surname value.
CCNo is the combination and would be your CustID

My If IsNull is to verify that a number has not already been assigned to
that record.
RCN is my equivalent of RecordID

Let me know if that helps.

=====================================================
=====================================================