databasedev.co.uk - database solutions and downloads for microsoft access

Splitting Microsoft Access Fields

Splitting a Single Database Field into Multiple Fields

There may be cases where through either bad database design, inheriting the database from a previous designer or importing data from an external source, that the data is not stored in the format that you require.

Examples of this may be Address details, where the whole address is stored in a single field, or with Name details, where First Name and Last Name are stored within a single field.

To ensure a correct design, it may be required that you split a single field into multiple fields.

The following example demonstrates this using Full Name, where you wish to store this as First Name, Last Name:

We begin with by creating a new query, and adding the Contacts table (that is currently storing Full Name as a single field) to the query design.

We have added the FullName field to the query design, then we need to add two further fields, which we will create for FirstName and LastName and will use the following string expressions:

FirstName: Left([FullName],Instr([FullName]," ")-1)
LastName: Mid([FullName],Instr([FullName]," ")+1)

Query design showing expressions to split single field into multiple fields

Running this query, you will now see that the FullName shows but also the FirstName shows all data that is to the left of the first space in the original field, and the LastName field shows all data after this space.

The query results, showing the fields after using the expressions to split the data