Mar 14 2007, 11:03 AM
I have a report where the address is shown in the format:
City State Zip
City State Zip consists of two textboxes and one combobox. The textboxes (city and zip) have a fixed width as does the State combobox. The state combox goes to tluStates where it uses a StateID and Abbreviation to pull up the correct state.
The problem is that the length of cities varies, the length of states is always 2 characters. If the city has a long name it bumps into State. If the name is short there is a large gap left on the report.
Does anyone know how I can evenly distribute these three separate areas where there is one space between City and State and one space between State and Zip? Thanks for any help.
Mar 14 2007, 11:05 AM
I'd concatenate them together in an unbound text box:
=City & ", " & State & " " & Zip
By the way, 'Name' is a Reserved Word and should never be used as the actually name of a field or object.
Mar 14 2007, 11:10 AM
Thanks, unfortunately, I can't make this work since 2 character abbreviation for State comes from a table like this:
And the report is bound to a different table that has StateID as one of the columns.
So, what I would get if I did this is:
Houston 14 79782
Any other ideas? Thanks for the help.
Mar 14 2007, 11:11 AM
You really don't need to use combo boxes in reports. You link the lookup table to the main table in the Record Source of the report and include the text value. Then you use the concatenation formula like I did before.
Mar 14 2007, 11:19 AM
Thanks for the help.
How do I link the lookup table to the main table in the Record Source of the report?
Mar 14 2007, 11:20 AM
The easiest way is to make a query to include all the fields you need from all the tables required and use that query as the Record Source.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here