Strings

If you already use Access frequently, you will know that a string is not something that you cut off from a ball of string and use around the house. It is instead a stream of consecutive characters. They are not limited to the alphabet but can be any character within the character set (0 to 255). This covers all alphanumeric and control characters. These can be different according to what language code page you are using, but there are still only 256 characters. A string is useful for displaying a message to the user or providing a caption. A string could be "Martin", or it could be "1234".

VBA provides a number of functions for concatenating (joining) strings together, removing sections, searching, and changing case (to upper- or lowercase). For example, if you have a string "Your answer" and another " is wrong", you can join these together into one string: "Your answer is wrong". You can also use a function to change the entire string to uppercase characters so it reads "YOUR ANSWER IS WRONG", or you can search for a particular word or set of characters within the string.

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 are n tables within the database." You do this by concatenating the string "There are", the variable n (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" when n has a value of 1, so that it is always grammatically correct.

Consider the simple example of a For..Next loop. The code is as follows:

For n = 1 to 5
    MsgBox n
Next n

The message box gives the value of n as 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 of n is 1." This will be displayed five times in all with the value of n incrementing 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, although n is numeric, VBA automatically turns it into a character string for concatenation.