Home	Excel	Access
\| 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

	more ...	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

Chars

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))
Table:
Sort:
AppendTo:	TransDate	TransType	TransaAcc	TransValue	TransRef
Criteria:		<>"TFR"
Or:

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