Category Archives: Office software

Hints, tips and solutions for office software

Portrait and landscape in one document

A relatively straightforward, but hopefully useful, Word tip. Sometimes you might want to include a page in landscape orientation, in the middle of a normal ‘portrait’ document – for example if you need to include a wide table or schedule of some sort. You can achieve this in Word by creating multiple sections in the document. Each section can have it’s own Page Setup settings. You can do this by using Insert, Break. But it is easier to let Word do the work for you as shown below:

Portrait and landscape - animation

 

 

To do this, position the cursor immediately before the place where the landscape section is to begin and choose File, Page Setup. In the ‘margins’ section, set the orientation to ‘landscape’ and from the ‘Apply to’ list choose ‘This point forward’:

word-portland-1.jpg

If you now look at a multi-page preview of your document you will see that the pages after the current cursor position are now in landscape orientation:

word-portland-2a.jpg

If you have your ‘non-printing’ characters turned on, then you will see the section break that has been automatically inserted:

Portrait and landscape - section break close up

If you need the document to revert back to portrait after the landscape section, you will need to click at the end of the landscape pages and use File, Page Setup to create another portrait section again from ‘this point forward’.

Advertisements

Mapping database address fields

Introduction

This item arose from an engagement letter project I was working on, but should be useful whenever you are trying to translate one set of addresses, or indeed any other, fields to another.

A typical situation would be the need to take the addresses from a database that refers to them as follows:

Address1
Address2
Address3
Town
County
Postcode
Country

and import or link them to our database which has a differently structured set of address fields:

Premises
Town
County
Postcode
Country

Obviously, if our original set of fields has no structure, we then just have to guess which fields are mapped to which other fields. The issue we want to deal with here is condensing the three individual address line fields into the one premises field.

Let’s look at this in stages:

Stage one: concatenation

First of all we will look at a section of an ‘Append’ query that will take our 3 original address1-3 fields and join them together, with commas as separators. The append query will then add this combined address field to the ‘Premises’ field in our current database:

Concatenation

Note that we have included a comma followed by a space between each address line, and used the ‘&’ to join all the bits of the address together.

This is all very well, but of course our premises address will look something like:

Concatenation result

This will make it pretty tricky to generate an address in a letter or on a label.

Stage two: carriage return and line feed

What we really need to do is to put each of the individual fields onto a different line. We can do this by replacing the “, ” bits with the characters that will create the required line feed. These are characters 13 and 10 and we need to enter them using the CHR() function as follows:

Chr(13) & Chr(10)

Here is the complete field:

Separate lines

and the result:

Separate lines result

Stage 3: allow for empty fields

We’re not quite there yet. If any of the address fields are blank, we will end up with blank lines in our new premises field. We need to use the Access IIF() function to check each field and only include it if it has something in it.

In fact it’s worse than that because if there is something in address2 but not in address1, we need to include the contents of address2, but not the carriage return/line feed that precedes it. The same issue occurs if there is something in address3, but nothing in either address1 or address2.

We think the following should do the trick – please let us know if you don’t think it will work properly, or if you are able to come up with a more elegant solution:

Premises: [address1] & IIf([address2]<>””,IIf([address1]<>””,Chr(13) & Chr(10)) & [address2]) & IIf([address3]<>””,IIf([address1] & [address2]<>””,Chr(13) & Chr(10)) & [address3])

Excel protection

After an Excel course I was running at the highly impressive Sage HQ in Newcastle, one of the delegates pointed out that it’s all very well protecting your spreadsheets, but how do you stop users cutting and pasting between unlocked data cells? The effect of this is to potentially mess up the references to the data cells affected even in the protected cells. Of course it would be possible to write VB code to prevent this, or to use form fields or some other method of data entry, but is there a simpler way?

In Excel XP and later, the protection capabilities have been substantially enhanced and allow choice as to what a user can and cannot do. In addition, there is the ability to ‘Allow users to edit ranges’. I wondered whether this might be the solution, so tried leaving all the cells in a sheet locked, but then adding the data cells to an ‘allowed range’ – in this case without setting a password. This did seem to work. When the sheet was protected the protection did not allow cutting and pasting in any locked cells (which was of course all the cells – including those in the allowed range), but because of the allowed range, with no password, data could be entered in these cells. I don’t know whether this is a well-known feature and obvious method of implementing protection since XP changed the protection options, or an unintended but useful consequence. I also haven’t yet tested the way it works thoroughly and am waiting to hear how well the solution works for the person who asked the question in the first place.

Excel protection