3

I'm trying to use Microsoft Access to start an employee database. However, I want the display of the Employee ID to be the initials and then their number. For example, John Smith with the ID 0002 is JS0002. I've been playing around with format but it doesn't seem I can do this if I put my EmployeeID as an autonumber. Thanks in advance!

Sally
  • 31
  • 1

1 Answers1

1

You control the way fields are displayed with the Format property. If you apply the format to the field in the table then it applies to all views of all records. You can't apply a different format to each record.

If you want to display their initials before the employee number then you need to build that in to a query.

This takes the first character from the left of each name component and combines it with a formatted number.

SELECT Left([FirstName],1) & Left([LastName],1) & Format(ID,"0000") AS EmployeeID
FROM Employees;

In my testing the result is exactly what you want. You can include this query in any form or report where this format is required. You can also include this as just part of a larger query to base your forms and reports off of.

HackSlash
  • 171
  • 6