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.

Advertisements

More time validation

I mentioned table level validation rules already. They’re handy for things like making it so that the date a job is finished has to be after the date a job has started.

You can also use them if you want to combine two sets of validation rules for a specific field.

So, say that you want to only allow a time between a set of times. That’s easy – you simply use a time validation rule at the field level. But if you also want to make it so that times can only end in :00 or :30 (so an appointment can only be made at half past or on the hour) then it’s trickier.

Here you need to combine table level and field validation.

1. Start by dealing with the field level validation for the set of times. Check it and make sure it works!

2. Now click the Property Sheet button (see Table level validation rules)

3. Now add a validation rule to make the minutes only able to end in :00 or :30:

  • Obviously you could add more Or conditions into this to allow ending with 15 or 45. Or even more to allow ending in 10, 20 etc…
  • My field name in this is jobTime. You’d need to change this to your field name

4. Don’t forget the validation text – make it helpful

5. Test and check that it works. Don’t forget that table level validation will only check the rule once you move off the row you’re entering data in.

6. Test the combinations – can you enter a time not in the range that ends in :00? What about a time not in the range that ends in :57? Or a time in the range that ends in :57?

There you go – table level validation magic!


Multiple Table Level Validation:

Now, you can also add more than one validation rule at table level if you’re clever. So, for example, if I want to combine my rule for making the date the job is completed after the date the job was started and my new rule for validating the minutes then I can do:

The trick with this is probably writing a sensible validation text message!

Validating times in Access

Oh, I forget so much these days.

I’m sure I knew how to do this, but the trouble with databases is that I only do this level of work once a year so I’m forever forgetting how to do stuff.

So, validating a time field in Access so that values can only be between certain times. Like the times a shop is open for.

Easy:

Between #09:00:00# and #17:00:00#

will do the job (in this case I want a time between 9am and 5pm). The #’s are the key thing that I forgot.

Image

I imagine this will work for Medium and Long Time fields as well. Probably anyway. You probably shouldn’t be using General Date field types so that’s not a problem.

Table level validation rules

Validation rules in Access are relatively straightforward.

Setting a range of numbers is fine, making a number positive is fine, choosing from a set of values is fine. But it gets more complex if you need to add multiple validation rules for particular fields or if you need to compare one field to another.

Say, for example, you want to make it so that the date a job is finished has to be after the date a job has started.

Standard validation won’t work in this case – you need table level validation rules instead. Fortunately these are pretty easy to deal with. More or less.

1. Open the table you need to validate within in Design View

2. Hit the Property Sheet button to pop the property sheet up. Make it as wide as you need to.

3. Write the validation rule in the way you’d usually do, using the same kind of ideas you might if you were dealing with fields in a query.

So, to deal with the date example, try this:

  • My field names are in this. You’d, obviously, change the field names to match your own.

4. Make the validation text helpful and useful

5. Try it out.

Note: the validation won’t immediately pick up and error when you enter the data. You need to click off the row you’re entering data in in the table (or click Save on the form etc…) for it to pick up the error and pop up the error message. That doesn’t matter – the key is that it’s checking that the data is valid and then giving a helpful error message if it’s not!