Categories
Blog

Text Formulas in Microsoft Access

Most people are adept at using numeric formulas because they are very similar between Microsoft Excel and Microsoft Access. However, text formulas are a little more complex, partly because people do not understand what they are or how to use them. A major part of the misunderstanding comes from the name; text formulas can do a number of things, including math with dates, but it is primarily a way

for you to manipulate text. It is commonly used for queries by more advanced users. When you first begin learning how to work with Access 2013, you probably skip over this function entirely, which is fine. As you gain more experience, you will begin to see how valuable text formulas can be because you will have been doing the work manually for a while.

Once you’ve spent time understanding Access and how to manage databases, you can start to venture into things like text functions to turn some of the painful manual processes into automated processes. Text functions spare you from having to manually count entries or having to create columns or rows that will count for you. Best of all, text formulas can be used for any field type, not just those that have text.

Text formulas can be very useful tools once you or members of your staff have seen what it is like having to do all of the work. However, they are very complex and usually they require you to learn coding to use them effectively.

How Can You Benefit from Text Functions?

When

you think of formulas, the only time you think of letters is when they represent variables. However, there are many other ways that you can manipulate entire words (not just variables) to get mathematical information from a database. The largest problem is reprogramming the way you think about math.

Perhaps the best example is physics. Everyone knows the equation E=mc2 (even if they don’t know what it means). You can think of the formula as shorthand. E is energy, m is the mass of an object, and c is the speed of light (squared as the 2 indicates). E and m are variables, but c is a known numeric value, which could be used instead of the letter. Text formulas are similar, but there are no set values. As shown in the example, you could be dealing with dates, complete words, or a number of other less common uses.

You can translate dates from the default state (such as 21-AUG) into a numeric value (8) to indicate just the month. This can then be used to find out how many sales were completed for each month, determine how long a project took to complete, or track which months have higher than average customer complaints. Text formulas can be as simple as the use in Access total rows. For total rows, you can examine text to see how many entries there were for a particular column. Like Excel, Access lets you format numbers with text formulas.

When Should You Use Text Formulas?

There aren’t any differences between using a text formula and a regular mathematical formula besides the fact that it works with text. The easiest way to work with Access text functions and formulas is when you are coding, which is why it is one of the more advanced features. There are a number of different ways to use text formulas with Visual Basic to return things like string information, average number of letters in data entries, formatting text, and parse functions.

If you want to use the text formulas in Access, the total rows are the easiest and best way to use them. The limitation is that the formulas are pretty much only to run totals. They won’t format any of the information in columns or convert anything, but you won’t need to use any code or learn anything extra to use them.

When needed, you can use text formulas when you import data. This is the easiest way of ensuring that the formatting is consistent.

Using Text Formulas

With the majority of text formulas being utilized either through code or total rows, there are not many ways to use it for your everyday work. If you go into Access Help, for example, it will not return any information on how to work with text formulas. If you have the time to learn code to simplify working with text fields, the text functions website provides some very basic information to give you at least a basic understanding of your options and how you can use text formulas when coding.

However, when you are importing information, particularly from text files, into your database, there are some very easy ways to use text formulas to make sure strings go where they should. Text files do not include tables. Instead, data is divided by tabs, spaces, or other breaks to indicate when a field is complete and the next field starts.

You can create a query that will use text formulas to do this for you. Depending on what information is available, you will need to adjust the formulas. The website Mead In Kent has created a page that explains working text formulas into queries to make importing data easier and with fewer steps. 

Leave a Reply

Your email address will not be published. Required fields are marked *