| Feedback | Creating user defined fields |

Creating fields from a string of text in an Access append query

This section looks at extracting or consolidating fields from strings of text that are held in records. The main functions used to achieve this are:

LEFT(), RIGHT(), MID(), TRIM() and VAL() Manipulating and extracting sections from strings of text

Access 2010 Step by Step

UK more ...   USA more ...

Access can create and save Import Specification Files for importing external text files into tables. These can work well with comma separated lists or regular fixed width lists of data. A string of text (the source file) can be chopped up (i.e. parsed) in order to populate a number of different fields within a record (in the destination table).

Sometimes however greater flexibility can be had by importing the entire text file into a temporary table, using one field per record (or more fields if the length exceeds 132 characters).  It can then be manipulated in a query with certain pieces of data extracted into fields and certain records (such as report headers) omitted. An Append type query will take data from one table and use it to add new records to another table.

A table containing text strings to be parsed

A table (Temp_Text) contains a single field (Chars) which contains some data imported from another financial program.

Temp_Text: Table
12/01/2006TFR  12-34-56 9080706HEADQUARTERS        0004560+ JAN SUBS            X   
14/01/2006DEP  12-34-56 9080706JIM                 0012356- INV 2466            X
17/01/2006DEP  12-34-56 9080706SALLY               0008500- INV 2510            X
22/01/2006PAY  12-34-56 9080706GARAGE              0013000+ 06-9998             X

We want to take elements from each of these text strings and put them in separate fields in another table.

Fin_Trans: Table (design) ox
  Field Name Data type Description  
   TransType  Text  transaction type used by Finance system  
   TransDate  Date/Time  date of transaction  
   TransRef  Text  reference of transaction  
   TransAcc  Text  account name  
   TransValue  Number  value of transaction  

An append query to convert and transfer the data

Convert FinTrans : Append query (design) ox
Field: f1:Left([chars],10) f2: Mid([chars],11,3) f3:Mid([chars],32,10) f4: Val(Mid([chars],52,7))/100* IIf(Mid([chars],59,1)="-",-1,1) f5:Trim(Mid([chars],61,10))
AppendTo: TransDate TransType TransaAcc TransValue TransRef
Criteria: <>"TFR"  

Not all of the imported data is required - TFR type records are excluded.

Functions used to manipulate text Description of purpose
Left(text_item, number_of_characters) Returns the specified number of characters from the left of the string.
Right(text_item, number_of_characters) Returns the specified number of characters from the right of the string. (not used in example above)
Mid(text_item, start_position, number_of_characters) Returns the specified number of characters from a start position in the middle of the string.
Trim(text_item) Removes any spaces from the beginning or end of the string.
Val(text_item) Converts a text string into a number value

The TransValue field also uses the IIF() function to change the sign of negative amounts (by multiplying negative values by -1).

Fin_Trans: Table ox
TransType TransDate TransRef TransAcc TransValue
DEP 14/01/2006 INV 2466 JIM -123.56
DEP 17/01/2006 INV 2510 SALLY -85
PAY 22/01/2006 06-9998 GARAGE 130

Another useful tool which was not required in the example above is to join (concatenate) two strings together. This can be done using the '&' (ampersand) operator. It takes the form: =Text1 & Text2  For example: ="Account: " & [TransAcc] could return: Account: Sally

file: accparsetext.htm Page last updated Apr14 2014