Envelope Problems

[millwheal]

I use Access 2003 running under Windows XP SP3.

I have a number of databases which I use [amongst other things] to produce envelopes and/or labels.

I use several different sizes of envelope, but use the same method for all envelopes and all databases: I create a report, with dimensions appropriate to the envelope for which it is to be used.
On the report, I insert all the name/address fields I require, putting them in the top left-hand corner of the report and defined as NOT visible.
I also have an equivalent set of fields in the lower centre of the report, which are defined as visible.
I have a 'Format Detail' event procedure that "shuffles" the address lines [to remove any all-blank lines], placing the results in the central, visible fields.

I run the reports in 'Print Preview' mode first, to ensure that they are properly formatted [I use the same databases on different PCs/printers, so this is a necessary precaution].
The first envelope [page one of the report] is always correctly aligned, as are all the other pages from three onwards. However, page two is always wrongly aligned, with the name/address appearing higher up the envelope/report than all the other pages. However, it is correctly aligned horizontally, so it appears not to be anything to do with the invisible fields in the top left-hand corner.
The amount by which the page two details are 'raised' varies according to the size of the envelope - the larger the envelope, the larger the displacement. It is also affected by the dimensions of the report compared to the envelope - if I set the report dimensions to as near as I can to the envelope dimensions, the displacement is small, but still there.

I have tried creating the report using Label Wizard and got the same results.

Any suggestions as to the cause would be welcome.

 

[GaryPanic]

try this based on your qry add a field called Fulldetails
everything in [**] is a feild within the table/qry


Fulldetails: [Insured] & IIf(IsNull([add1]),"",Chr(13) & Chr(10) & [add1]) & IIf(IsNull([add2]),"",Chr(13) & Chr(10) & [add2]) & IIf(IsNull([add3]),"",Chr(13) & Chr(10) & [add3]) & IIf(IsNull([add4]),"",Chr(13) & Chr(10) & [add4]) & IIf(IsNull([postcode]),"",Chr(13) & Chr(10) & [postcode])


Insured
add1
add2
add3
add4
postcode

is add4 is missing it just shuffles up the postcode

hth

 

[millwheal]

Thanks for the suggestion.

This is a much neater way of removing blank lines from addresses than the method I was using, and I will use your code from now on.

However, this does not solve the problem of the second page/envelope being vertically displaced.

 

[GaryPanic]

can you zip up a file so we can have a look (make sure it's empty - except a couple of test addresses ) - and one of the gurus might have a look - I have a look and see if i can locate the issue (i am no guru -- side note the coding was not mine - just something i "borrowed ")

 

[SOS]

Gary:

You don't need all of those IIF's to handle null values. All you need is to concatenate the potential blank parts using the + instead of the & and it will remove them if blanks are returned.

 

[millwheal]

Before resonding to the specific points raised, I should say that the various databases were converted from Access 97 only a few weeks ago, and I can't recall having these problems when I used Access 97. None of my 'envelope reports' were defined to have the same dimensions as the envelope!

Having done some more work on the problem, it seems to be associated with the dimensions of the report rather than anything else.

I mentioned that the report dimensions were not exactly the same as those of the envelope. If I change the report dimensions so that [including margins] it equates (more or less) to the envelope size, everything seems to be OK. Access has a mind of it's own when it comes to dimensions, and if I set the report dimensions to be exactly the same as the envelope defined as the printer stationery, it doesn't work - I get blank pages and messages to the effect of the report width being greater than that of the defined stationery. Trial and error enabled me to find report dimensions [slightly less than the actual envelope] that do work.

I don't think this is the real solution to the problem; I think it's just a workaround. I will try to sort out a version of one of the databases to upload for a guru to inspect.

Turning to the use of '+' instead of the Iif and '&', it doesn't seem to work. If I simply replace Iif and '&' as suggested, nothing appears when I preview the report. Perhaps I'm doing something wrong, but I'm not sure the '+' approach would work anyway - carriage returns need to be included, but how would that be done just when they were needed, as opposed to after every field, which would potentially leave blank lines in the middle?

 

[SOS]

Using the + instead is used like:

[FirstName] & " " & [LastName] + Chr(13) & Chr(10) + [AddressField] + Chr(13) & Chr(10) + [City Field] + ", " + [Region Field] + " " + [Postal Code Field]

Which should yield the appropriate thing if fields are blank. I used the & in places it is expected and the name concatenation assuming a first and last name.

 

[GaryPanic]

Hmm interesting ..
I might try this- but the version I have been using is doing the job and I usually cut and paste the code and tweak it to suit my requirements - but i will make a note of your version and see if it works.

as tot he orginal post - Access does seem to have a mind of its own on certain things - i have heard that labels can be a nightmare - I think Access is trying to be too helpful and cocking things up -

 

[SOS]

I used to do the IIF way too until I learned the + method. It can take a bit of working with it to get exactly the right ones (the stuff I posted here is just from memory of using it). But once you do, you have a really nice, compact, method to deal with nulls instead of all of the IIF statements. But, if the IIF's work, and you can just copy and paste - then by all means use it. I just like to find the most "compact" means of accomplishing a task and then try to learn to use it.

Same with getting rid of all of the

If MyBoolean = True Then
   Something = True
Else
   Something = False
End If

Where this is shorter and does the same:

If MyBoolean Then
   Something = True
Else
   Something = False
End If

But then there is an even shorter method:

Something = MyBoolean

Which is probably the most compact but does the same as the other two.

 

[millwheal]

SOS - thanks for the '+' and '&' explanation; I've now used your example and it works perfectly. I can now copy that into all my various databases that produce envelopes. This will be easy to do as I use the same object definitions to ease copying of 'good things' from one Db to another. Its a very useful tip!

 

[SOS]

Glad I could be of some assistance. And, believe me - this is a never ending story in which you (and me of course) are always learning new things. Kind of fun that though as there seems to be always something else to learn and therefore it never gets boring.