Today is part two of Is it a company or is it a person? I don't know. Let's go check. We're going to discuss how to display the company or person name with a little bit of query action and some functions and some other funny parts.

So in yesterday's video, we did this where we made a little report showing either the company or the person just by simply using CanShrink. Well, what if you want it to be a little more professional? So today we're going to learn how to do it with a query, some calculated fields, and a few other things.

Let's talk about the prerequisites first. We are going to use calculated fields today, so if you've never used this both in a report and in a query, go watch this video if you haven't done that before. You should know how to use string concatenation. That's putting two string fields together.

Also, as a side note, go watch my video on null math and I'll explain why a little bit, but this is a pretty good one. Speaking of null, you should know what null is and how to check for null values with the IsNull function. We're going to watch this and finally we're going to bring it all together with the if function. If, right? I F. Yes, I'm silly when I say if, but it's so you remember it because a whole bunch of times I get people sending me emails wondering why it's not working and they did I F F. It's not if. It's not Silence of the Lambs, right?

Fava beans, it's if, and me being silly will hopefully let you remember that. Okay, go watch all these videos if you haven't yet. They're on my website. They're on my YouTube channel. They're all free and then come on back.

All right, so the first thing we're going to do is create a query that's going to have the fields we want in the report in it, and we're going to make a calculated field that says, hey, if I got a company name, I want to see the company name as who it's who the statement's prepared for. If there is no company name, use the person's name, okay?

Okay, so we're going to go to Create -> Query Design. I'm going to bring in my customer table and then bring in just the fields that you want on the report. If you want the ID, great, bring it in. If not, I just need first name, last name, company name, and we'll just do address.

Okay, now over here we're going to make a calculated field called prepared for. Who is the statement prepared for? I'm going to zoom in so you can see it better. That's Shift+F2 to open a zoom window with bigger text, right? We'll call this prepared for:

I'm going to use the if function. So if, what's the first thing I'm checking for? Well, if the company name is null, so I'm going to use If(IsNull([CompanyName])). Now come on, what is the value to use if that's true? If the company name is null, then I want to use the person's name. So here I'll put first name & " " & last name. What's the condition if it's false? If the company name is not null, then I want to see the company name, right? All right, so CompanyName. And that's it, right? Three parts to my if function. What's the condition? This will return a true or false, right? IsNull(CompanyName). If that's true, if there is no company name, then give me first name and last name. Otherwise, I want that company name. All right, hit OK. Let's save this as my customer Q, customer query, and now when I run it, there you go, there's your prepared for right over here. Right, I got a company name for these four fields, so they could see the company name over here, and if not, I got a person's name. Now, if they're both blank, I don't know what to tell you. You could do a nested if function with another level and say, you know, if both of those things are null, then give me the customer ID and say, you know, statement prepared for customer 14 or whatever you want to see.

But we're just going to assume you've got either the first name, last name, or the company name, okay? And if anyone wants to see that third option, I've got videos on my website for nested ifs. Here's a video where I show nested ifs, for example. I put a link to this down below.

So now that we got this set up, now we can use this as the record source for our report. So close this, save changes, yes. Let's go back to our customer report design view. Go up to the report's properties. Let's go to the data tab, change CustomerT to the CustomerQ, that query we just built.

Okay, close that. I'm going to get rid of these extra sections in here. We don't need that space in there right now. Just clean this up a little bit and let's slide these fields down just a touch, right about there. Now, if you open up existing fields, you will see there's that prepared for right there. We can drag that over here and for its label, let's say "Statement Prepared For". Slide that over a little bit, right about there. Let's grab this and slide you over right about there so it fits nicely. There we go. Let's make this a little bit bigger. This comes in as gray. I hate that. Let's go to format and change this to black. All right, and now save it and let's print preview.

And there you go, statement prepared for Amicron, for Enterprise, for Beta, for William Riker, for Malcolm Reynolds. See that? It gives you the company name if it has it; if not, it gives you a person's name. And if it's got both, it gives you the company.

But, oh no, we're not done yet. Let's make this look a little better. First of all, let's turn these borders off. I know I wanted to keep them on here for a little bit so we could just see them there, but let's go back to design view. All right, I'm going to select everybody, go to format, go to shape outline, and pick transparent. That way we don't have to see those borders. You wouldn't see those on a printed report.

Okay, now let's bring this stuff together. Instead of having these two fields, just like the first name, last name. I hate that; that looks dumb. Let's bring them together, right? Let's use this little string concatenation here. So I'm going to get rid of that label and slide you over, maybe bold it right. Here, all right, let's bring up its property to close the field list, open up the property sheet, click on that guy, and what we're going to do is I'm going to make this a calculated field. So I'll change the control source, but I'm also going to change the name. All right, because I only want the name to be the same if it's exactly bound to that control source, if it's the same thing, right? I'm going to change it. So we're going to call this preparedForText because now I'm going to change this control source. Shift+F2, zoom in, = [statementPreparedFor] & [preparedFor], and that's a field name. Okay, there we go. And now if we look at it, save it and preview it, look at that. It looks a lot better, right? It's right next to each other.

Let's do the same thing for first and last name. Delete last name. Let's make first name bigger. Okay, click on it. I'm going to change this to let's call this personName. You don't want it to be the same, and this is now going to be = [FirstName] & " " & [LastName]. Hit OK. Let's save it and preview it. Okay, looks good. See, looks good.

And oh, I'm still getting that blank line there. Why is the CanShrink not working? Well, because what's actually in there? There's a space there. A space is not null, is it? Now, there are a couple of things you could do. You could trim this whole thing. All right, let's do this. Say Trim([FirstName]) & " " & Trim([LastName]). Trim gets rid of leading and trailing spaces, and if this is blank and that's blank, then you're just going to be left with a space in there. Let's see if that works. Save it, preview it. OK, look, that worked fine. That's one way to do it.

Another way is to use that null math that I mentioned earlier. Instead of using the trim function, if we know that if you add anything to a null value, the result is null, then you could do this too. Put a plus there and a plus there. What this is saying is take the first name, add it to a space. Well, if the first name is null, then the result of this is null, and then if you add another whatever it is to it, null plus anything becomes null. So this whole thing becomes null then, if either one of those fields is blank. All right, and if you do it now, same results.

Now that I'm thinking about it, I really like the null math, but I kind of like the trim option better because this way, if you just have one of those things, you'll get it in there like this. It'll either say just Richard or just Rost. It's better, I think, than having a whole null field there, but either way works, whatever you want, you know, whatever works for you. So now you got your prepared for and you got your address down below it, and now you can use this customer query for anything else too. You could use it in the calculated field in the form or whatever. Whatever you like. So totally up to you.

I just show you the Legos, and you can put them together however you want. If you like learning with me, if you like to get some learning done and you like my style and you have fun, well, I got tons and tons of lessons on my website. This is the kind of stuff, this null stuff with the functions and all that, these are covered on my expert lessons. It's beyond the basics. It's not just building simple forms and doing some formatting. It's working with functions, formulas, if functions, that kind of stuff. If you like all those functions in Excel, you'll love these lessons. I cover all the date time functions and the rest. OK, you name it, it's in my expert lessons. And you don't need programming to build some really great stuff in Microsoft Access. And that's what my expert lessons are all about.

But there you go, that's your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.


TOPICS:
Creating a query for report fields
Using calculated fields in queries
Checking for null values using IsNull
Using the if function in queries
Building a prepared for calculated field
Replacing CustomerT with CustomerQ in report
Formatting and cleaning up report design
Using string concatenation in reports
Removing borders from report fields
Using Trim function to remove spaces
Using null math for null value handling
Combining first and last names in reports

COMMERCIAL:
In today's video, we explore part two of 'Is it a company or is it a person?'. We'll look at displaying names professionally using queries, calculated fields, and string concatenation. First, we create a query that checks if a company name is null to display either the company name or the person's name. We'll also cover prerequisites like handling null values with the IsNull function and string concatenation. Next, we design a report, set up proper formatting, and ensure no extra spaces show up using Trim functions and null math. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.