Access VBA Macro Programming

Concatenation

Concatenation is how you join strings together, generally using the & sign. It is extremely

useful when you want to display messages. Suppose you are writing a program to display the

number of tables in a database. Your program counts up the tables and stores the number in a

variable. You could easily display the variable to the user, but what would it mean to that user?

When writing software, you want a clear message displayed to the user, such as, “There

arentables within the database.” You do this by concatenating the string "There are", the

variablen(which contains the number of databases), and the string "tables within the

database". You can also introduce code that changes the first string to read “There is” whenn

has a value of 1, so that it is always grammatically correct.

MsgBox "There are " & n & " tables within the database "

Consider the simple example of a For..Next loop from the section titled “Looping” in

Chapter 4. The code is as follows:

For n = 1 to 5

MsgBox n

Next n

The message box gives the value ofnas it increments, but it is just a message box with a

number and does not give the number any meaning. By adding a string, you can make a more

user-friendly message:

For n = 1 to 5

MsgBox "The value of n is " & n

Next n

The message box will now show the text “The value ofnis 1.” This will be displayed five

times in all with the value ofnincrementing each time. Don’t forget to leave a space after the

word “is,” or your message will look peculiar and may be difficult to read.

There is no limit to how many strings and values you can concatenate in this way. Note

that, althoughnis numeric, VBA automatically turns it into a character string for

concatenation.

Splitting Strings

You may need only a part of a string in your code. For example, say you have a two-figure

reference number at the beginning of the string that you need to use elsewhere in your

program, but you wish to show only the name:

"12Richard"

To pull out the name only, you can use theMidfunction:

x=Mid("12Richard",3)

46 Microsoft Access 2010 VBA Macro Programming