Address Block
By Richard Rost
3 years ago
Professional-Looking Address Block for Reports
In this Microsoft Access tutorial, I'm going to show you how to create a professional-looking address block for your reports. Specifically, we'll focus on ensuring that fields such as city, state, and ZIP code are not spaced out, and that there are no blank lines when data is missing.
Susan from Redlands, California (A Gold Member) says: I'm using your Invoicing database and it works real well, however is there any way to make the address block look better? The fields like city, state, and ZIP code are spaced out, and I added an "Address2" line and if there's nothing in it, it leaves a blank line there. Any ideas?
Prerequisites
Recommended Courses
Address Block
Put all of this on the same line in a text box. I just separated it here for readability:
=((([FirstName]+" ") & [LastName])+Chr(13)+Chr(10)) &
([Address]+Chr(13)+Chr(10)) &
([Address2]+Chr(13)+Chr(10)) &
((([City]+" ") & ([State]+" ") & ([ZIP]+" "))+Chr(13)+Chr(10)) &
([Country])
Up Next...
Format this address block. Change color, bold, font size:
Keywords
access 2016, access 2019, access 2021, access 365, ms access address block for reports no blank lines, Suppress blank lines on a report, can grow, can shrink, Avoiding blank Address lines in reports, how to eliminate blank rows, Removing blank lines from report
Subscribe to Address Block
Get notifications when this page is updated
Intro In this video, I will show you how to create a professional-looking address block for your Microsoft Access reports. We will look at how to combine individual address fields like first name, last name, address lines, city, state, and zip into a single formatted block, handle optional address fields to avoid blank lines, and use concatenation and null math to make your address section clean and flexible. If you find that your current reports have uneven spacing or unwanted empty lines, this tutorial will help you fix those issues for a more polished result.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor Richard Rost. Today we're going to talk about making a professional-looking address block for your Microsoft Access reports.
Instead of looking like this, where everything is all spaced out because you have individual text boxes for the different fields and you get blank lines in there, we are going to make it look like that - everything is all nice and together. No blank lines. All that good stuff.
This question comes from Susan in Redlands, California, one of my Gold members. Susan says, "I'm using your invoicing database and it works real well. However, is there any way to make the address block look better?" You don't like my address block? Why? The fields like city, state, and zip code are spaced out and I added an address two line. And if there's nothing in it, it leaves a blank line there. Any ideas?
Well, yes, Susan. In the invoicing video, I intentionally kept this simple to keep the database simple for beginners. But with a couple little tips and tricks, I'm going to show you how to make it look a lot better, just like that one. This is one of those "learn to walk before you run" things. I teach you the basics first, then we get a little more advanced as we go along. That's how all of my classes work.
Now, I am going to consider this an expert-level video. What does expert mean? Well, it's not quite beginner, but it's not developer either. Expert is kind of sandwiched between the two. That means you need a little bit more knowledge than the average person who is starting with Access, but we do not need any programming for this.
Here's what you will need to know. First, watch my invoicing video. If you have not yet watched it already, I teach you how I build the invoice and the order entry system and all that cool stuff. You will need to know how concatenation works - that's taking two strings or two fields and putting them together into one. So watch this if you do not know how to do that. We're going to use some null math, which is a cool trick to get strings to be ignored if you're trying to concatenate them or add them onto a null value. Go watch this video. This is very important.
And go watch my video on Can Grow/Can Shrink to get rid of those extra lines between fields if something's missing, which won't work today. We'll talk about why. Go watch all of these. If you do not know this stuff already, they're all free videos. They're on my YouTube channel. They're on my website. They're free. You'll find the links down below in the description. You can click on them and go watch those. So go do that and then come back.
Here I am in my TechHelp free template. This is a free database. You can download a copy if you'd like. It's very simple. It's got a customer form; every customer can have orders; and on each order, you can print an invoice.
Now, when I built this invoice, I built it nice and simple. First name, last name - these are all individual fields. So each field is going to take up exactly the space that you give it. City goes there, state goes there, and so on. No way around that.
Now, what we can do is, for things like first name and last name, and then city, state, and zip, we can concatenate those and make them one line. Address is fine. Country is fine. So let's get rid of last name. We'll make first name bigger. We'll open up its properties - double click. We'll call this one - now we'll call it "first last name." Whoops, my mouse is doing some weird stuff. I clicked down here. First last name. Then we'll make this equals first name and a space and last name. That will put those both together in one text box.
Same thing down here. We'll get rid of city, state, and zip. Click and drag. We'll make this one - let's rename this one CSZ4 - equals city and a space and state and a space and zip. Save it. Close it. Let's take a look at it now.
A lot better. That is fifty percent better than what it was before. That looks nice.
Now, Susan mentioned that she added an address two line. That's ok. Lots of people have address two lines - you have address one, address two. This should be two lines or, that should technically be on the second line. You can put an address, you can do a little line break in there and make a two-line address. I do not like doing that though. I do generally put an address two line in a lot of databases that I build.
Let's go to the customer table and let's - oh, I have this open. Hang on. You have to close any forms that are bound to that table or have data from that table. Let's insert a row right here, insert row, and we'll drop an address two line right there. Save that. Close it.
I'm going to leave it blank just to show you what happens when we add it to our invoice. Let's go back to the orders. Oh, wait, one more step. Our invoice is based on this order invoice Q query. So we have to add it to that too. Because remember when we built this, we only brought in the fields from customer that we needed. Address, city, state, and so on. So we have to add address two as well. Add it over here as long as it's in the query right there. Save it. Close it.
Now we should be able to add it to our invoice orders invoice. Right click, design view. I'm going to slide these guys down like that. Let's just copy address, copy, paste, control C, control V. Open up this guy. We'll change this to address two and do not forget to change the name. Save it. Close it.
If I print an invoice now, you'll see that's empty. If there was something in that field, it would put it in the form - but if we put it right in the table... let's put address two in here. I'll just put a bunch of x's. If there is something in the address two, then you'll see it there. But if not, it's blank. We do not want to see blank. We do not like blank lines. That looks nasty. That does not look very professional.
Now, if there was nothing to the right of it, we could get away with using Can Grow/Can Shrink. Watch this. Let me just take all this stuff and move it out of the way here. Slide all that up here. So there is nothing to the right of this address block. This is all empty over here. You could select all of these, go to their properties, right click properties, find Can Grow/Can Shrink under format - slide all the way down here. Set Can Grow and Can Shrink to Yes and Yes.
Can Shrink means if this is null, if there is nothing in it, then it can disappear and its height will just become zero as long as there is nothing to the right of it.
Now you can see it's gone. Address two is gone because there is nothing over here. This works fine if you're just doing a mailing, like my letter writer, where you are sending a letter, because usually there is nothing over here. It's empty space. But with an invoice you usually have stuff next to it like this. You have stuff over here, you have stuff over there. So this trick is not going to work. Watch.
Because you have stuff over here, it spoils that Can Shrink. So that is not going to do. How do we get around this? The trick is to take concatenation to the next level. We concatenate all of these things in one field, one big text box. So literally delete all of that stuff. Make this one big box - that big.
Now what goes in here? We have to put all of those fields in there and we have to put new lines at the end of each line. We cannot use the new line character - there's a special character for those of you who do programming, vbNewLine. We can't use that. But there is a character combination we can use which is character 13 and character 10. Put those two together and you get a new line, carriage return. It's basically just like pressing enter, that's all that is.
So let's go to Data, let's go to Controls. I'm going to zoom in here, shift F2. Click here, shift F2.
So this first thing is first name and last name, and then we're going to add to it a Chr(13) and a Chr(10), just like that. After that, we're going to add the address, so it's going to be address and then Chr(13) and Chr(10). After that, address two, and I'm going to copy and paste because I'm tired of typing in Chr(13) and Chr(10) - that's our new line. Then city and a space and state and a space and zip, and Chr(13) and Chr(10), right, and then finally country.
It's much easier if you can see it laid out like a normal address block, but you can't hit enter in here so it messes things all up. That's what it looks like. I know it's weird. Save it. Close it. Let's take a look.
Now address two is missing, but our formula did not take that into consideration. We're going to use null math to get rid of anything that happens to be blank. If it's blank, do not show it.
So back to design view. Let's go back into here again. Now try to envision each line. This right here is one line. So put that in parentheses like so. I'm going to change this to a plus and a plus here. As far as this goes, first name could be missing and then I do not want that space there. So we're going to put another set of parentheses here like this and change that to a plus.
So it'll get first name if it's there. If that's null, we won't have the space there. Plus last name and then Chr(13) and Chr(10).
The next line starts here - it's going to be the address and these two guys. We're going to change those things to pluses. That way, if address is null, this whole thing evaluates to gone and you won't get that blank line.
Same thing around this stuff - address two. Change these to pluses. We still need these ampersands, though, because if one of these evaluates to null, we do not want the whole thing evaluating to null, so there have to be some ampersands.
The next line is right here. Put those around parentheses. City, and then - now this one is a little more complicated because you want to have a break between city and state. Plus state, then another ampersand, then zip. Then you have to have parentheses around this whole line, like that. Then you're going to go plus and then one more set of parentheses, and then finally country on the end.
I think I got it. Let's see. Tab. Save it. Close it.
There you go. It's all about the null math, people. Learn your null math. This way if any of these things are missing, it doesn't ruin the whole thing.
For example, if I go into customerT and put in address two, then open it back up again, I get address two. If it's not there, address two plus the Chr(13) and Chr(10) in parentheses evaluates to null and disappears. If I'm missing a first name, I still see Rost with no space in front of it, because remember what happens is first name plus the space - this will evaluate to null and it will add the last name. If last name is also null, that plus this stuff will basically delete the whole line. Watch. If I don't have a name, and I check it again, there we go. The whole line disappears and that will work for every line.
It's all about the null math, folks. Learn that null math.
That is a pretty crazy equation. I will put a copy of that equation on my website and in the notes, so you can just copy and paste it if you want to instead of trying to retype the whole thing.
If you want to learn more cool stuff like this, in my Access Expert Level 8 class, this is where I start building my order entry system with the invoicing and all that cool stuff. I have lots and lots of lessons on my website to teach you how to be a cool Access guy like me.
But that is going to be your TechHelp video for today. I hope you learned something. I enjoyed doing this. I hope you did too. Give me some comments down below. Tell me what you thought of today's lesson.
Live long and prosper, my friends. I'll see you next time.Quiz Q1. What is the main purpose of creating a professional-looking address block in Microsoft Access reports?
A. To avoid blank lines and space out address components evenly
B. To separate each address field into individual lines for clarity
C. To combine multiple fields into a single field with no blank lines when some data is missing
D. To ensure the address block fits only in mailing label layouts
Q2. What is concatenation in the context of building address blocks?
A. Splitting a single field into multiple fields for display
B. Taking two or more fields and combining them into one string or field
C. Sorting address fields alphabetically for formatting
D. Deleting unused address fields from the report
Q3. Why does the "Can Shrink" property not always work when trying to eliminate blank lines in an address block?
A. It only works when each address component is in its own text box
B. It works even if other data is to the right of the shrinking field
C. It only works if there is nothing to the right of the shrinking text box
D. It always eliminates blank lines regardless of layout
Q4. When combining address elements, what are Chr(13) and Chr(10) used for?
A. To represent capital letters for each address field
B. To insert a tab space between fields
C. To create a new line or carriage return in the address block
D. To concatenate numbers in address fields
Q5. What issue can occur if you concatenate fields like first name and last name using an ampersand (&) rather than a plus (+) in Access when some fields are null?
A. The entire concatenated string will display as an error
B. The entire expression will evaluate to null if any field is null
C. Only the null values are removed but spaces remain
D. Using ampersand always gives the same result as plus
Q6. Why is "null math" important when building an address block in an Access report?
A. It helps keep the field names in alphabetical order
B. It ensures that if a component of the address is missing, the final output does not show unwanted blank lines or spaces
C. It automatically fills missing fields with zeros
D. It is only required when using VBA code and macros
Q7. Which character combination is used in Access expressions to create a new line within a text box?
A. Chr(11) and Chr(12)
B. vbNewLine only
C. Chr(13) and Chr(10)
D. Space and underscore
Q8. What is the main advantage of combining all address fields into one text box with conditional logic for null values?
A. It creates a single string that always shows every field, whether blank or not
B. It allows the address block to ignore missing fields and prevent awkward gaps or blank lines
C. It makes the report design process faster by eliminating all conditional formatting
D. It automatically generates a mailing label as a separate report
Q9. When designing an invoice in Access, why might you not be able to use "Can Grow" and "Can Shrink" to solve blank line problems?
A. Because invoices must use only one font type
B. Because there are usually other report controls to the right of the address block
C. Because Access does not support these properties on reports
D. Because these properties only work in tables, not reports
Q10. What should you do if you want to add a new "Address 2" field to your address block in an Access report?
A. Add it to the customer table, add it to the relevant query, and update the report to include it
B. Only add it to the report, not the table or query
C. Replace the "Address 1" field with "Address 2"
D. Create a macro to import "Address 2" from Excel
Q11. If both first name and last name fields are null when using the concatenated address expression with "null math," what happens to that line in the address block?
A. The line still appears with a blank space
B. The line is suppressed and does not show at all
C. The report displays an error message
D. Only a question mark is printed in place of the missing data
Q12. Why is it important to use parentheses when applying "null math" in complex concatenation expressions in Access?
A. Parentheses are not needed with null math
B. Parentheses ensure certain fields are always displayed
C. Parentheses group parts of the expression so that the null evaluation works as intended, eliminating blank results
D. Parentheses prevent the expression from being calculated
Q13. What is the primary goal of using these advanced techniques to format address blocks in Access reports?
A. To create a flashy, colorful design for marketing purposes
B. To simplify data entry during customer creation
C. To produce a clean, professional-looking address with no awkward blank lines or spaces
D. To automatically export all addresses as PDF files
Answers: 1-C; 2-B; 3-C; 4-C; 5-B; 6-B; 7-C; 8-B; 9-B; 10-A; 11-B; 12-C; 13-C
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone is all about creating a professional-looking address block for your Microsoft Access reports.
When you build Access reports, it's common to place each part of the address - first name, last name, address, city, state, zip, and so on - in separate text boxes. The problem with this approach is that if certain fields are blank, such as an optional Address 2 line, the result is empty gaps in your address block. This ends up looking unprofessional and wastes space.
Our topic today comes in response to a question from one of the students who is using my invoicing database template. The concern is that fields like city, state, and zip code appear spaced oddly, and if Address 2 is blank, it leaves an unattractive blank line in the printed report. This is actually a common issue, especially when working with separate fields for each part of the address.
When I first built the invoicing database, I deliberately kept things simple for beginners. However, there comes a point when you're ready to take the next step and improve the look of your reports. That's the focus of this lesson, which I would say falls into the "expert" category. This means you should have a little experience with Access, but don't worry - you don't need to write any code for this.
There are a few concepts you should already be familiar with to get the most out of this lesson. First, you should understand concatenation, which is the process of combining multiple fields into one string to make a readable address. It's also important to know about a technique often referred to as "null math," which controls how empty fields behave when you're concatenating them together. If any of these ideas are new to you, I recommend checking out my previous tutorials on these subjects, as well as my video on the Can Grow and Can Shrink report settings. All of these resources are available for free on my website and YouTube channel.
Let's look at my sample database, which uses a simple template. Each customer can have orders, and each order can produce an invoice. Typically, the invoice report has separate fields for each part of the customer's address. This is why each field takes up a specific area, and blanks can occur if any fields are empty.
To make the address block more visually appealing, you can combine fields such as first name and last name into one text box. Similarly, you can combine city, state, and zip into another. This straightforward concatenation cuts down on spacing issues. For example, you'd remove the separate last name field, enlarge the first name box, and then combine both names into it. Do the same with city, state, and zip code. This already makes the address block look much cleaner.
However, many databases include an Address 2 field for things like apartment numbers or suite designations. The challenge is that if Address 2 is empty, the space it occupies on the printed report leaves an unsightly gap.
Modifying the database structure starts with adding Address 2 directly to the customer table, then updating the relevant query (for example, your order invoice query) to include this new field. Once that's done, you add Address 2 to the report layout alongside the other address fields.
If you simply place Address 2 in a new text box on the report, the blank line issue remains when nothing is entered for that field. You might think you can resolve this with the Can Grow and Can Shrink properties on the report fields. This feature allows a control to shrink if it has no data, but it only works effectively if there's nothing else to the right of it on the report. Since invoices typically have data in the columns next to the address block, this method doesn't solve the problem for most layouts.
The real solution is to bring all address fields together inside one large text box by concatenating them, inserting line breaks where necessary. In Access reports, you can't simply press Enter to start a new line, but you can use the Chr(13) and Chr(10) characters, which represent a carriage return and line feed, to force a line break.
The approach is to take each part of the address block and combine them, separated by Chr(13) and Chr(10) for line breaks. For each optional field, like Address 2, you apply null math, which means you only add that line if the field is not empty. If it is empty, the line - including the line break - is omitted.
To achieve this, you use careful grouping and concatenation with plus signs and ampersands, so that fields that are null simply don't appear and don't leave extra spaces or blank lines in the result. For example, if there's no first name, no leading blank space appears before the last name. If Address 2 is missing, both it and the corresponding line break are skipped, producing a tight, professional address block.
With this setup, you can test different scenarios by removing or adding data to your address fields and see that the address block automatically adapts, showing only the fields that are present, each on its own proper line without any gaps.
The key to this whole approach is mastering null math within your concatenated expressions. This ensures that blank fields don't produce strange results or leave empty lines in your finished address.
If you're interested in using the exact formula, I have it available on my website for you to copy and use in your own projects.
To learn more techniques like this, my Access Expert Level 8 class continues with building the order entry and invoicing system and covers these kinds of topics in further detail. There are plenty more lessons on my website to help you improve your Access skills.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Creating a professional address block in Access reports
Combining first name and last name into one field
Concatenating city, state, and zip code into a single line
Adding Address 2 as an optional field in the table
Updating queries to include new address fields
Inserting Address 2 into the report layout
Avoiding blank lines when Address 2 is empty
Using Can Grow and Can Shrink properties for text boxes
Limitations of Can Shrink when fields exist beside the address block
Building a single text box for the entire address block
Using Chr(13) and Chr(10) to insert line breaks
Constructing the address block with line breaks using concatenation
Applying null math to remove empty lines dynamically
Adjusting formulas to account for missing field values in the address
Ensuring no extra spaces appear if first or last name is missing
Testing the dynamic address block with various field values |
