Access 2007, labels

Question

I'm not a real experienced user of Access. I'm trying to print address labels from a query I did. I keep getting Parameter Value Error and it asks for Trim.  If I skip this step and it showes my labels the top line with {first_name}{last_name} comes out error. If I put them on seperate lines.

{first_name}

{last_name}

it works. I have the font set to 8 to try and make this fit and it still won't fit. I am using an 1" x 2-5/8" label and all the text should fit on one line but it won't let me do it. I filtered out all blank spaces and checked for any other possible errors but no luck.  Any ideas what the heck I'm doing wrong? And how can I fix it.

Answer 1

The curly braces are just the way the labels wizard shows them in the prototype label.  Your query is fine.  The problem appears to be in the report, and may be in the ControlSource property of the text box for the first line of the label.  Open the report in design view and see what the ControlSource property of the text box for the first line is.

If you want to use the CanShrinkLines function from my demo file, first copy the function into any standard module in your database.  Then in design view of the report delete all the text boxes below the first one, resize the first one so its bottom edge is at the bottom of the detail section by dragging the bottom edge down.  Change the name of the text box to txtFullAddress and change its ControlSource property so it calls the CanShrinkLines function in the same way as in my demo.

Answer 2

Sorry... haven't used the label wizard much and had forgotten the curly brackets!

Try basing the label report on a query doing the concatenation:

SELECT [ECSL Membership List].First_Name & " " & [ECSL Membership List].Last_Name AS FullName, [ECSL Membership List].Address, [ECSL Membership List].City, [ECSL Membership List].St, [ECSL Membership List].Zip

FROM [ECSL Membership List];

Answer 3

When I typed these { } type brackets, this is what is shown when setting up the label with the wizard also in the design view.

 This is in the SQL view of the query;

SELECT [ECSL Membership List].Last_Name, [ECSL Membership List].First_Name, [ECSL Membership List].Address, [ECSL Membership List].City, [ECSL Membership List].St, [ECSL Membership List].Zip

FROM [ECSL Membership List];

Answer 4

You might be interested in the file Concat.zip in my public databases folder at:

https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

This little demo file includes a number of methods of concatenating values, including one for doing so as address lines.  It uses a modified version of the CanShrinkLines function published by Microsoft.  You'll find the function in the basConcat module in the database.

By calling this function the address can be shown in a single text box control in the labels report.  Any Null lines are suppressed, so you don't get any blank lines in the address.  A report in the file demonstrates this using data from Northwind.  Note how when passing the fields into the function the first line is made up of the first and last names concatenated together with a space between them in the same way as John suggested.

=CanShrinkLines([First Name] & " " & [Last Name],[Address],[City],[Zip/Postal Code])

Any number of fields can be passed into the function as its argument is a parameter array which accepts a variable number of values separated by commas.

The report in the file is not a labels report as such, as it includes explanatory text, but a true labels report would be much the same with the removal of all the extraneous stuff, leaving only the single text box control in the detail section.

Answer 5

Please open the query in SQL view and post the SQL text here. Did you mean to use curly brackets {} - which Access will NOT recognize - or square brackets? You may want to set the Control Source of a textbox to

=[First_Name] & " " & [Last_Name]

to display "Joe Doakes" in one textbox.