Company or Person
By Richard Rost
2 years ago
Show Company or Person Name in MS Access
In this Microsoft Access tutorial, I will show you how to conditionally display either a company name or a person's name on your reports. Learn the beginner method using property settings and explore an expert approach with functions for better results. Perfect for ensuring clean and accurate statement printing.
Marcelino from Hephzibah, Georgia (a Silver Member) asks: In my table, I store fields for Company Name, First Name, and Last Name. Sometimes I have all three, but other times I only have a company name or a person's name. I want to ensure that when printing statements, the Company Name shows up if it is available, and if not, the person's name is printed instead.
Members
There is no extended cut, but here is the database download:
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Recommended Courses
Up Next
Keywords
TechHelp Access, Conditional display in Microsoft Access, Microsoft Access report design, company name or person name Access, Access Can Shrink property, Access conditional text display, Access report tutorial, print statements Access, Access functions for reports, beginner methods Access, expert methods Access
Intro
In this video, I'll show you how to set up your Microsoft Access reports to display either a company name, a person's name, or both, depending on which information is available in your database. You'll learn how to add a company name field to your customer table, enter test data, create a blank report, assign the correct record source, and arrange the address block layout. I'll cover how to use the Can Shrink property on both text boxes and the detail section so the report looks professional when certain fields are empty.
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost. Today, we're going to discuss displaying either the person's name or their company name or both conditionally depending on what you have in your Microsoft Access database.
Today's question comes from Marcellino in Hefsabud, Georgia, one of my silver members. Marcellino says, in my table, I store fields for company name, first name, and last name. Sometimes I have all three, but other times I only have a company name or a person's name. I want to ensure that when printing statements, the company name shows up if it's available and if not, the person's name is printed instead.
All right, so there are a couple of different ways we can do this. First, I'm going to show you the beginner way, which is pretty simple and just involves some property settings on your reports. Then we'll go into the expert version, which doesn't involve any programming, but it requires a couple of functions. Obviously, the expert method will give you better results, but you can just do the beginner one if that's what you want.
As far as prerequisites go, of course, I expect that you've taken my Access Beginner Level One course. It's free. It's on my website. It's on my YouTube channel. Also, go watch this video on Can Grow and Shrink. That's how you can make fields that don't have any data in them disappear on your reports, which is what we're going to talk about.
First, this will be the beginner method. Okay, so here I am in my TechHelp free template. This is a free database. You can grab it off my website if you want to. In here, I have a customer form, with some personal information like first name, last name, address. I don't have company names. Let's add that real quick. Right-click, design view on the customer table. We'll put the company name on the bottom, and I'm just going to drag that up so it's right below the first and last name right there. Just keep them all together.
All right. Let's switch over to datasheet view. Let's put some data in here. I want a couple of people with company names and a couple of people without. So I'll put Amacron in here and we'll do Enterprise. Let's get rid of Diana's name. So we have a person with no name, but Beta Z Inc. And we'll get rid of Picard too. We'll do Sheto Card.
Okay, so we have a couple of people that have all three, a couple that are just companies, and then the rest are all just people's names. We're going to work with that. Save that. Let's close it. Let's go make a quick person report.
Come down to your reports. I have a blank report already set up. It's just got all my basic settings like margins and stuff. So I'm going to copy and paste that one. I cover how to build that in the blank template video where I show how I built this database. It's on my website. Again, you'll find links down below.
I'm just going to call this my customer or customer report. Let's right-click design view. Now let's set this statement up. Let's just pretend we're doing an address block. You've seen it at the top of any letter that you send out. It's got the company name, person's name, address, city, etc. So let's just delete this.
I'm going to go up here to where the report properties are, double-click. That'll bring up the property sheet. Change the record source. You have to go to the data tab or the All tab. Set the record source equal to our customer table. Now this report is going to get its data and fields from the customer table. We can close this now.
Let's go to report design. By the way, I double-click to open up or close the ribbon permanently. That's how you show or hide it. Add existing fields is over here on report design. Let's bring over the fields that we need for this report. I'm going to do first name, last name, company name, and I'm holding down the control key, by the way, click the first one, and then hold the control key down and click, click, click. Let's just bring these over. First name, last name, company name, address, click, drag, drop them there.
For an address block, we don't need these labels. So let's just delete the labels. I have first name, last name, put them right next to each other. Company name can be a bit bigger. Let's resize that and then address. We'll go right below that. Then you'd continue on with your city, state, zip code, all the other stuff. These fields get the point across.
Let's save this and print preview it and see what we got. Right-click print preview. There we go. We're going to leave the borders on these for now. Normally I would turn these borders off. We'll do that later, but just so you can see what we've got on here. Notice these ones are blank. This one has no name. This one too. All three. And then of course we've got William Records missing a company and the rest of them down below are missing companies.
Now, if you watch the Can Grow, Can Shrink video, you know that if there's no data in these fields, if we turn Can Shrink on for both the text boxes and the detail section, then these should disappear. Provided there's nothing else horizontally on the same line that you buy themselves, you can't have a label over here. It's not attached. You can't have some other fields over here. There's nothing on that line.
So how do we do that? Design view. I'm going to select, you can hide the address too if you want. That's up to you. Select all of those fields. Right-click go to properties. On the format tab, we're going to scroll down to Can Shrink and change that to yes. You can double-click to change it to yes. You also have to change the Can Shrink property in the detail section to yes. This allows this to shrink up. If you don't do that, it won't work. That's the number one thing people always ask me and I cover that in the Can Shrink video. Also, make sure there's nothing else horizontally over here. You can't have a picture over there or some other fields or whatever.
Now save it. Right-click. Print preview. There we go. These have all three. These ones are missing a person's name. These ones are missing a company name. And I can go through and you can hide, turn off the borders or whatever else you want to do.
Now this is a very simple way to go about doing it. What if you want this to look a little more professional? What if you want, for example, it says on top "Statement prepared for" and then the company name. Or if there is no company name, "Statement prepared for" the person's name? Well, we can do that using a query and some functions and we'll talk about that in tomorrow's video. So tune in tomorrow, same bat time, same bat channel. Or if you're a member, you can watch it right now. That's one of the benefits of being a member.
That's going to be your TechHelp video for today. I hope you learned something. Live long and prosper my friends. I'll see you tomorrow for part two.
A special thank you and shout out to our Diamond Sponsors. Next, we have Juan Soto with Access Experts Software Solutions, Manufacturing Experts Specializing in Access and SQL Server. Juan is a 13-time Microsoft Access MVP. You can check them out at accessexperts.com.
Another shout out to Sammy Shama from Shama Consultancy. Sammy is a certified Microsoft Office specialist, and he not only offers access application development, but he also provides one-on-one tutoring services. If you need someone to hold your hand and help you with your access project, Sammy is your guy. Check them out at ShamaConsultancy.com.
TOPICS:
Displaying name or company conditionally
Adding company name field to customer table
Entering sample data for testing
Creating a blank report in Access
Setting the record source for a report
Adding existing fields to a report
Customizing the address block layout
Using Can Shrink property on text boxes
Configuring Can Shrink property on detail section
Previewing and adjusting report layout
COMMERCIAL:
In today's video, I'll show you how to conditionally display either a company name or a person's name in your Microsoft Access database. We start with the simple, beginner method using basic report property settings. Then, I introduce a more advanced technique that doesn't require programming but involves a few helpful functions. Perfect for those with a mix of company names and individual names in their data. This video builds on concepts from my free Access Beginner Level One course and the Can Grow and Shrink tutorial. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What is the primary goal of Marcellino's request in this tutorial?
A. To print only the first name and last name of customers.
B. To ensure that the company name shows up if available, otherwise display the person's name.
C. To eliminate all customer names and display only addresses.
D. To remove all empty fields and display only non-empty fields in reports.
Q2. What are the prerequisites mentioned before starting this tutorial?
A. Access Beginner Level One course and a general knowledge of Excel.
B. Access Beginner Level One course and the Can Grow and Shrink video.
C. Access Intermediate Level course and basic VBA programming knowledge.
D. Experience with SQL Server and Access Advanced Functions.
Q3. Which view in Access allows you to modify the design of a table to add new fields?
A. Datasheet view
B. Report view
C. Design view
D. Layout view
Q4. What is the purpose of the 'Can Grow' and 'Can Shrink' properties in Access reports?
A. To allow the fields to increase or decrease font size.
B. To make fields with no data shrink and disappear, helping to clean up the report.
C. To change the color of the fields based on the data.
D. To automatically adjust the margins of the report.
Q5. Which fields are recommended to be brought over to the report for creating an address block?
A. Only the first name and last name fields.
B. Company name, only if it's available; otherwise, omit it.
C. First name, last name, company name, address, city, state, zip code.
D. Only the address and company name fields.
Q6. If there's no data in certain fields in a report, what is the effect of setting the 'Can Shrink' property for both the text boxes and the detail section to 'Yes'?
A. It increases the size of the text boxes to fit the page.
B. The text boxes with no data will shrink and disappear, and the detail section will adjust accordingly.
C. It highlights the fields with no data in a different color.
D. It prevents the report from being saved.
Q7. What advanced method is hinted at for displaying either the company name or the person's name more professionally in the report?
A. Using complex VBA code.
B. Employing advanced SQL queries with JOIN operations.
C. Creating a query with some functions to conditionally display labels.
D. Writing a complete macro to manage the report display.
Q8. What is the benefit of being a member on AccessLearningZone.com mentioned in the video?
A. Early access to future tutorials, among other benefits.
B. Discounted rates on Access beginner courses only.
C. Complete access to all advanced programming courses in VBA.
D. Personal tech support for Access database issues.
Answers: 1-B; 2-B; 3-C; 4-B; 5-C; 6-B; 7-C; 8-A
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 covers how to display either a person's name, a company name, or both conditionally in your Microsoft Access database.
Marcellino from Hefsabud, Georgia, one of our silver members, has asked a great question. In his table, he stores fields for company name, first name, and last name. Sometimes, he has all three, but other times he only has a company name or a person's name. He wants to ensure that when printing statements, the company name shows up if it's available, and if not, the person's name is printed instead.
There are a couple of ways to achieve this. First, I'll show you a basic method that involves adjusting some property settings on your reports. Then, I'll share a more advanced solution that doesn't require programming but will involve a few functions. The advanced method yields better results, but the beginner method is sufficient if you're looking for something quick and simple.
Before we dive in, make sure you've completed my Access Beginner Level One course. It's free and available on my website and YouTube channel. Also, familiarize yourself with the Can Grow and Shrink functionality, which allows fields without data to disappear on your reports.
Let's start with the beginner method. I'll use my TechHelp free template, which you can download from my website. This template includes a customer form with personal information, such as first name, last name, and address. I'll add a company name field to the customer table.
First, open the table in design view and add the company name field below the first and last name fields. Switch to datasheet view and enter some sample data. You should have a mix of records: some with both company and personal names, some with only company names, and some with only personal names.
Next, open a blank report template where I've set up basic settings like margins. Duplicate this template and name it something intuitive like "Customer Report." In the design view of this report, set the record source to the customer table. This ensures the report pulls data from the correct table.
Add the necessary fields from the customer table to the report: first name, last name, company name, and address. For aesthetic purposes, remove the default labels, arrange the fields in an address block format, and resize them as needed.
Save the report and preview it. Initially, you'll notice that some records will display blank fields, depending on the data. To make these fields disappear when they are empty, enable the Can Shrink property for the text boxes and the detail section of the report. This makes sure that if there's no data in a field, it won't display extraneous blank space.
After adjusting the properties, save and preview the report again. Empty fields should now be hidden, making the report look cleaner.
While this method is straightforward, you might be looking to add more professional touches, such as a conditional statement like "Statement prepared for" followed by either the company name or the person's name. For that, we can use a query and some functions, which I'll cover in a future video.
This concludes today's tutorial. If you want step-by-step instructions and a complete walkthrough, visit my website for a full video tutorial.
Live long and prosper, my friends.
Topic List
Displaying name or company conditionally
Adding company name field to customer table
Entering sample data for testing
Creating a blank report in Access
Setting the record source for a report
Adding existing fields to a report
Customizing the address block layout
Using Can Shrink property on text boxes
Configuring Can Shrink property on detail section
Previewing and adjusting report layout
|
