Mailing labels formatting

[Keith Sayers]

I have encountered a puzzling problem formatting a mailing labels report. It has five text boxes : Personal, Body, Street, PostBox and Suburb+State+Postcode - for all of which the CanShrink property is set to Yes. On formatting, if the Personal field is empty the Body field does not move up, if Body is empty the boxes below do not move up, but if Street is empty then the fields below that do move up. The report is based on a query with Iif functions in each field which yield "" if there is no incoming data. From testing with VarType I find that both the top two fields, if empty, contain a NULL, but entering such as 'If Null then height="0"' into the report field source has had no effect. It is not a serious problem - I doubt that my client, a charity, will grumble, and the resultant labels are good enough for Australia Post - but it is annoying me that I cannot get them neat with all fields closed up. I would be interested in anything anyone could suggest. I am using Access2K on Win2K.

[June7]

Expression would have to be in VBA code for the Detail section Format event. Format events execute only in PrintPreview or direct to printer.
If IsNull(Me.textboxName) Then Me.textboxName.Height = 0

However, since the empty string ("") allows rows to 'move up', make sure the first two controls have empty string if null is encounterd, assume both fields are text type:
=Nz(Personal)
=Nz(Body)

Are you printing to label sheets? If the data compacts due to empty rows, then how can it align with the labels?

[Keith Sayers]

On Monday 03 September 2012 June7 wrote:> Expression would have to be in VBA code for the Detail section Format> event. Format events execute only in PrintPreview or direct to printer.> If IsNull(Me.textboxName) Then Me.textboxName.Height = 0 Thank you for the help - but I do not get a Height option, though if I type in.height = 0 - deliberately using the miniscule to see what would happen - thenthe line is accepted with the option changed to Height. i.e. it seems to agreethat it knows something about height. Of the options available in the drop downlist the only likely alternative seemed to be SizeToFit but that did nothing. Would you know of some other workaround that I might try please?> Are you printing to label sheets? If the data compacts due to empty rows, then how can it align with the labels?------------------------------------------------------------------------------Keith Sayers keiths@apex.net.au6 Clambe PlaceCHARNWOOD, ACT 2615,Australia http://www.apex.net.au/~keiths------...--------------

[June7]

The only other suggestion I have is to concatenate the fields into one string with forced returns and line feeds in the string. The code would use conditional statements, something like:

IIf(IsNull([Personal]),"",[Personal] & Chr(13) & Chr(10)) &
IIf(IsNull([Body]),"",[Body] & Chr(13) & Chr(10)) &
IIf(IsNull([Street]),"",[Street] & Chr(13) & Chr(10)) &
...

[Keith Sayers]

Revisiting this to report progress. I never did diagnose that problem but have found a work around. It is my practice when I encounter such obstinate problems to close them up, walk away, turn to other matters but allow my sub/conscious mind to occasionally think them over. Every now and then I will get an idea on the lines of 'perhaps this might work' or 'perhaps that could be worth a try' and sometimes one such will lead to a solution. In this case I was puzzled as to why formatting mailing labels should not be a straightforward exercise. It is something I thought Access had had right from the beginning (and I go back to version 1) so why should it now be difficult? I went back through my old software and found I still had Access v2 so used that to create a table with the same structure as my problem database but with only seven entries - carefully constructing those seven to provide every possible combination of the presence and absence of name, organisation, PO box, street address and town/state/postcode. Then a query to pick those up and used the wizard to create a mailing label report. It worked (of course!) - produced perfectly formatted labels with the text boxes automatically moving upwards when the control above was empty. Just what I wanted. So then I carefully copied those three - table, query and report - into my problem database alongside the extant contents - again they worked perfectly, so it was not a software problem. The next steps would be tedious to recount in detail, but I cautiously expanded the 'test' table until it contained all the data from the problem table, at each step re-running the mailing label report, then likewise developed the test query, rerunning that until I had everything in, then deleted 'problem' table, query and report, renamed the test table, query and report to what the problem entries had been and I had just what I wanted. A long way round but it did eventually get me there. What I seem to have done is to make a practical application of the KISS principle - I pass on the experience in case it be of help to anyone else. :-)=

[June7]

Interesting! Want to provide the database as a template for others to examine? Follow instructions at bottom of my post.

[Keith Sayers]

> Interesting! Want to provide the database as a template for others to examine?

Certainly - herewith (I hope) the bare essentials database I compiled in Access v2. Having moved it up to Access2K I first replaced these records with those from the full database (some 2,000+) and then, one by one, added the 20 or so fields in that latter, checking at each step.

[June7]

Thanks for providing the database, unfortunately it won't open in Access 2010 at all. Access 2007 insists on converting but then doesn't actually save. I tried importing into a blank db. The table and query import but the report will not.

[Keith Sayers]

Curious indeed! I know nothing of Access versions beyond 2K so can onlyspeculate on possible explanations - do you get any sort of an error message when trying to open the report? However what I have now done is to go back to my 'basic essentials' v2 database - Address.mdb - copied it over to my Win2K hard drive - there opened it with Access2K, accepted the invitation to convert it to 'current edition', saved it under a new name - Address2K.mdb - closed everything down and then reopened. It did reopen as a 2K database and table, query and report all functioned correctly. I made a few minor changes in the table (just to be sure of having invoked 2K procedures), retested, resaved and reclosed. I attach a copy and would be interested to hear whether you can do anything with it. Incidentally the reason all this has arisen is that in retirement and needing mental occupation I am offering my Access skills to voluntary and community organisations using databases for such as membership records. This one came from a refugee support charity in Sydney.

[Keith Sayers]

Later - sorry, I seem to have lost the ability to attach a database, the 'Add Files' button is not responding to my click, I will try again later. :-(=

[Keith Sayers]

Trying again to attach the Access2K version of the database ...
Address2K.mdb

[June7]

When I try to open the query I get an error: Your Microsoft Access database or project contains a missing or broken reference to the file 'utility.mda'. I OK through that and then get: Undefined function 'UCase' in expression.

Thanks for trying but no need to dedicate more time on this.