Splitting Fields

Splitting Fields

If you have previously been using one field to store data in, for example a single field for a person's name, and you now want to split the data that is stored in this table into two fields, i.e. forename and surname, then you will need to use an Update Query.

Firstly, add the two new fields to the table, with DataType Text, and then save this table.

Create a new query, and add the table to the query. Change the query type from Select (which is the default) to Update. Drag the first field, ForeName, to the query grid. Then, in the 'Update to' field, type this:

Left([FullName],Instr([FullName]," ")-1)

Repeat the process with the second field, LastName, but the 'Update to' field will now be:

Mid([FullName],Instr([FullName]," ")+1)

You can then run this query, and the ForeName field should now contain everything that was to the left of the first space in the original field, and the LastName field should have everything after this. Note that there will always be cases where the data is not automatically updated correctly, as in 'Mary Ann Smith', so you will need to do a quick visual check to resolve any discrepancies.

As ever, when writing any sort of code that will make changes to the data in your application, always work on a copy of your data until you know that it is correct.

Top

Last modified at 06/06/2006 15:00:52