|| Feedback | Creating user defined fields ||
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 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 (Temp_Text) contains a single field (Chars) which contains some data imported from another financial program.
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|
|TransValue||Number||value of transaction|
|Convert FinTrans : Append query (design)||ox|
|Field:||f4: Val(Mid([chars],52,7))/100* IIf(Mid([chars],59,1)="-",-1,1)|
Not all of the imported data is required - TFR type records are excluded.
|Functions used to manipulate text||Description of purpose|
|Returns the specified number of characters from the left of the string.|
|Returns the specified number of characters from the right of the string. (not used in example above)|
|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).
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||© MeadInKent.co.uk 2014|