When we last saw our heroes, Rocky and Bullwinkle, they had just finished Access Fear Factor Level I and discovered to their horror that their carefully crafted movie review had been truncated at 255 characters. "Where are we, Rocky?" asked the full-antlered Bullwinkle. Rocky replied, "Hokey smoke, Bullwinkle! It looks like we’re back in MS-DOS!"
In this installment, we’ll take care of that problem—plus solve a couple more. Then you’ll see how to bypass Access’s Report feature by using Word to display and format the data.
When I converted my nascent database from Excel to Access, Access correctly sensed that my Review field exceeded the Text field limit and used the Memo field format. Text fields can hold a maximum of 255 characters. Memo fields can hold up to 65,536 characters, or roughly 11,000 words.
Picking up where we left off last time, with the table displayed, click on the Design tool at the top of the floating Database dialog box to display the Table design form shown in Figure 1. At the bottom of the list of field names, click on Memo next to the Review field.
Contrast Figure 1 with Figure 2. Figure 1 shows a Text field’s settings, whereas Figure 2 shows the settings for a Memo field. Although the settings look similar, the Text field has two settings that the Memo field does not have: Field Size and Input Mask. "Aha!" you might be tempted to say. Ignore those two fields—they’re red herrings!
Instead, notice the @ in the Format field of both. The @ sign is used to represent the characters of the data (the review, in this case) and format it in some way. Feel free to experiment with it. But, for now, simply delete the @ sign and click the X to close the Table design form. Finally, say Yes to saving changes.
In the Database dialog box, double-click the data table (Movies), as shown in Figure 3.
When you widen the height and width of the Review field to display its contents, you now can see the entire review, not just the first 255 characters (see Figure 4).
To widen the field, drag the column and row borders (in the gray area to the top and left of where the actual data are displayed) of the field you want to expand, as shown in Figure 5.
The downside of Memo fields is that they cannot contain character, word, and paragraph-level formatting such as bold or italics. You could, instead, use a Hyperlink field, which you could click to open a formatted copy of the review in Word (for example). That would, to some extent, defeat at least one purpose: conveniently displaying all your movie review information at the same time. Alternatively, you could use an OLE Object field, which would let you display the review alongside other movie data. However, both Hyperlink and OLE approaches defeat another purpose—conveniently entering your movie data in one place.
I’ll revisit both of these options briefly in Part III of this series. For now, however, let’s enjoy the freedom-from-formatting that this "limitation" affords us. That is, the inability to use formatting limits you to using words themselves to convey excitement, sarcasm, and dismay. How archaic!