Report formatting

This came up in class yesterday so I thought a quick blog post might be a handy way of getting the key information online quickly.

Access report formatting can be awkward, particularly when you want to produce a letter or something interesting. Having fields from the database in separate boxes leads to nasty gaps and information getting cut off and so on. The solution is to get to grips with some of the more fancy formatting techniques.

These essentially use an Unbound Textbox to combine pieces of data. You need to use a new unbound textbox (not a label – make sure you pick the right icon!) from the top of the Design tab.

Because it’s an unbound  textbox you have to start with =. Any text you want to add goes in quotes “Like this”. Any field from your data source goes in square brackets [likeThis] (you need to get the fieldname just right by the way).

You then link bits together using an ampersand (one of these: &).

So, an address for a letter might go:

="Dear " & [userTitle] & " " & [userSurname] & ","

Note the space in the middle. Any pure text I want to include goes in quotes – and there needs to be an ampersand between everything (if there’s not you’ll get an error thrown – check ampersands and quotes first when this happens).

That’s a fairly simple bit of concatenation (you can find more examples on my website).

You can also add longer unbound textboxes for the body of a letter – see te screenshot below. To do that though you might need to add paragraph breaks. These get a little tricky – look at the screenshot and I’ll explain what’s going on underneath.

report formatting

Paragraph breaks get done using the & Chr(13) & Chr(10) & sequence. Check you have ampersands between everything.

This works by using Ascii code (google it…). Essentially every key on a keyboard has a numerical code associated with it. Some of the keys don’t print anything on the screen though – like the arrow keys or the return key. Chr(13) is a carriage return code – it moves the cursor back to the left hand side of the box. Chr(10) then adds a new line by moving the cursor down one line. Combining the two codes (with concatenation) has the effect of giving you a new line. Adding more than one set will give you a paragraph break.

Date formats – these can be changed within a report as well. The code to change a numeric date (like 25/02/2014) to a written date (like 25 February 2014) is shown in the screenshot. You might have to play around a little with this – don’t forget the round brackets – essentially Format$ is a function so needs round brackets.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s