Solved How do I formatt an Address in a report. (1 Viewer)

[progrob]

I'm trying to print addresses using report.
The spacing between fields is off because names, addresses, and cities all have different lengths. Using layout view helps a little but still leaves too much space between fields on my report. If I reduce the field size in layout then longer first names or towns get cut off or overwritten by the nex

I'm hoping to have addresses print out looking like:

John Doe
123 Easy Street
Anytown, CA 99999

Jameson Hacker
789 Home Street
Realylargetownname, WA 99009

Instead of being aligned with the field sizes in layout view.

Any ideas?

 

[Gasman]

Concatenate the fields into one control?

 

[Pat Hartman]

Select (FirstName + " ") & LastName As fullname, Address, Select City & ", " & State & " " & Zip As Addr2 From yourtable;

Notice the two concatenation operators in the first expression. The & is the standard concatenator operator for VBA. It ignores Null values so if the expression was FirstName & " " & LastName and FirstName was null, you would end up with blankLastName. The + is the standard arithmetic operator for addition but it doubles as a second concatenation operator with different properties. I.e. It respects Nulls so by using the + to concatenate the first name and the space, the result will Null if the firstName is Null.

Other tidbits. If you use the + as the concatenation operator but both fields are numeric, they will be added rather than concatenated. If one or both fields is a string, then the number and the string will be concatenated. i.e. 1 + 1 might = 2 or it might = 11 depending on the data types of the two fields.

 

[Gasman]

Hmm, I was thinking it would need vbCRLF or the Chr() equivalent between ALL fields?

 

[Pat Hartman]

You can use the vbCRLF to make one variable of THREE rows. I generally use three variables but you still have fields to concatenate which is what I was showing. Frequently when concatenating these types of strings, one or more of the fields could be null and you don't want to see the extra spaces that would occur if you just always used the & to concatenate all the fields.

 

[progrob]

Thanks, got it to work.

 

[Gasman]

Thanks, got it to work.

Well, please post your solution. It might help someone else in the future.
After all that is what this site is for?