Hands on: Getting attached to Access 2007

Confused?
At this point regular readers have every right to be confused. Access 2007 introduced both multi-valued data types and the attachment data type. Both allow multiple pieces of data to be placed in one field. I think multi-valued data types are bad, but I like attachment data types. How come?

Well, they both break the atomicity rule but - and it is an important but - multi-valued fields break the rule and the result is that searching through simple data becomes very difficult. Attachments break the rule, but they don’t make anything more difficult and make some operations much easier.

Rules are important and I don’t believe they are made to be broken but I also don’t believe they are sacrosanct. In this respect we could argue, as Barbossa does in Pirates of the Caribbean: The Curse of the Black Pearl that they are “more what you’d call ‘guidelines’ than actual rules”. But what do you think?

Don’t quote me but...
Jake Wilkins has inherited an Access database of quotations and the problem is not so much the quotations, as the quotation marks. Some entries have none, some are wrapped in straight double quotation marks and some in curly double quotation marks.

Jake wants to search for entries starting with each of the three types. The quoteless quotations aren’t a problem; it’s the others that are giving him grief. As Jake says, the quoteless ones are easy, as they respond to the usual form of Access query

Like "ab*"

finds every entry beginning with the characters ‘ab’ and any number of characters thereafter (the * wildcard fetches all the subsequent characters).

The straight double quote mark is the symbol used by Access to identify the start and end of the string for which it is to search (as in "ab*" in the example above). We have to tell Access that we want it to look for the same symbol in the database field it is searching. Oddly enough, this is done by doubling up the symbol, so the query is

Like """ab*"

There are three straight double quotes in front of the ‘ab’, the first indicates the start of the string and the next two tell Access to search for the character. This will find quotations like “About time too.”
In order to find the curly quoted quotations we need to show Access the opening curly quote character. Write the query to the point where you need the character

Like "

Start up the Character Map (Start, All Programs, Accessories, System Tools) and find the character. In the Times and Ariel fonts it’s about two-thirds of the way down the character set. Click on it, click Select and move back to the query grid. Control & V pastes in the character and you can finish the query.

Like "“ab*"

This finds entries like “Absolutely!”. These queries work for entries in memo fields too. For this query a curly double opening quotation mark character has been imported from the Character Map.

Earning your stripes
Jillian Grant has written in, having seen an Access report that had the rows displayed on different-coloured lines. She says that these made it really easy to read across each line. Rows with white backgrounds and ones with grey backgrounds alternated down the report, and she’d like to do this with her reports.

She’s using Access 2007 (no, really) and that happens to be an excellent choice in this instance - adding stripes has been made very simple in Access 2007 whereas in 2003 it required some VBA programming.
With a report in Layout view, click alongside the body of the report to select it (when selected the rows are outlined in orange/yellow). Now look in the Font group on the Home tab for the ‘Alternate Fill/Back Color’ button and click the arrow alongside the button.

You can choose Automatic, which gives grey and white stripes, or you can pick a colour either from the batch of mainly blue ‘Access Theme Colors’ or from the ‘Standard Colors’. To return a report to a non-stripy state, choose the ‘No Color’ option.

The ‘Alternate Fill/Back Color’ button lets you add alternate coloured stripes to your report.