|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Concatenation By Richard Rost 
5 years ago
Combine Two or More Fields Together In this video, I will show you how to put two or more values together in your Microsoft Access database using concatenation. You can join text strings, numbers, currency values, and all kinds of things. I'll show you how to concatenate using a query, and then directly inside a form or report. Bridget from El Paso, Texas (a Platinum Member) asks: I'm creating a combo box on my order form to pick a customer. It seems you can only see first name OR last name in the combo box while it's closed, but they both are in there when it's open. How can I see them both? MembersMembers will learn how to use Null Propagation to get rid of the comma if there is no first name in a Last, First scenario. We'll also see how to concatenate currency values (and make them look like currency values) as well as numbers with a specific format, such as phone number or SSNs.
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!
Links
Keywordsmicrosoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, concatenate, concatenation, concatenating, combine text values, combine strings, get rid of comma, null propagation, concatenate related rows, concatenate multiple records, concatenate fields in report, concatenate fields in form, join fields, format function, trim function
IntroIn this video, I will show you how to use string concatenation to combine multiple fields—such as first name and last name—into a single field in Microsoft Access. We will cover how to set this up in queries for use in combo boxes, and I'll also demonstrate how to concatenate fields directly inside forms and reports. You'll learn how to create calculated query fields with an alias, the basics of building concatenated display fields, and practical tips for making your database forms and reports more user-friendly.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, I'm going to show you how to combine two or more fields together using concatenation in Microsoft Access. We're going to see how to do it in a query so that you can use that query inside of a combo box, and then I'll also show you how to do it directly inside of forms and reports. Today's question comes from Bridget in El Paso, Texas, one of my Platinum members. It says: I'm creating a combo box on my order form to pick a customer. It seems like you can only see first name or last name in the combo box while it's closed, but they both are in there when it's open. How can I see them both? Well, Bridget, to do this, we're going to use something called string concatenation. Let me show you how it works. Here I am in my TechHelp free template. This is a free download from my website. You can go grab a copy. I'll put a link down below in the links section. Now if you look at my customer form and then go to Orders, you'll see I've already set something like this up. Here it is right there. You've got last name, comma, first name in the customer combo box, even though last name and first name are two different fields in the customer table. How do I do that? It's something called string concatenation. It's a query right here. I'm going to right click and go to Design View. Bringing in the customer table, here's the customer ID, and then I created a calculated query field, which, if you've never done a calculated query field before, go watch my video on calculated query fields. Right here you can see the concatenated fields. I'm going to click on that and hit Shift F2 to zoom in so you can see it right there. I'm creating a new field called LF. That's called an alias. Then I'm simply concatenating last name and quote, comma, space, quote – that puts a little comma space in there between last name and first name – and then an ampersand and then first name. That's string concatenation. All right, I'll hit OK, and then when I run the query, it puts them together: last name, comma, first name. Now you can use this field, this LF field, in your combo boxes and your other queries and forms and reports and so on. I cover string concatenation in a lot more detail in my Access Beginner Level 9 class. In fact, I'll show you how you can use string concatenation directly in your forms and reports like this. You can take two fields, last name and first name, and put them together directly inside the report. Let me show you how to do this real quick. So I'm going to close this query. Save changes? Sure. I'm going to go to a report. Now I don't have any reports except this Order Invoice here, but I'm going to use a blank report. All right, let's just right click, Design View, and let's say I'm going to make a simple customer report. All right, so I'm going to open up the properties. I'm going to go to Record Source and bind this to my customer table, directly to the table. All right, save that, close it. Now I can go to Add Existing Fields. Let's bring in the first name and the last name. Bring them right here into the detail section, and I can delete this other field that's in here. Let's line these up so they're horizontal. I'm going to delete the labels. I'm going to put first name over here and then last name next to it like this. All right, shrink up that detail section, and we don't need these other bands too. Let's get rid of those. Just shrink them up to nothing. Okay, let's save this and let's give it a Print Preview. Right click, Print Preview, and that's what I've got: first name, last name, separated. Now I want to put them together in the same field without having to use a query. So, back to Design View. I'm just going to copy one of these guys, copy and paste. Let's slide it over here next to it. All right, there. Okay, open up the properties, go to All. Let's give this guy a name first. Let's do last name first name, so LF, we'll call it. Now for the Control Source, delete last name. We're going to put our own Control Source in there, which is our concatenation equation. So I'm going to hit Shift F2 to zoom in so you guys can see it, and then put my equation in here: =LastName & ", " & FirstName And since I didn't use spaces in my field names, I don't have to worry about putting the brackets around there. Access will do it for me. All right, very important: don't put spaces in your field names and your table names. I cover that in my Access Beginner Level 1 class. Go watch it. It's four hours long. It's free. I teach all the basics like that – what to do, what not to do. All right, let's save this, Control S. And now I'm going to close that. And let's right click, Print Preview. And there we go. There's our concatenated field. So you can do that using a query, or you can do it right inside your forms and reports. Now of course, keep in mind, if you do this in a form, this field is not editable, so it's pretty much only for display. But you can also use it inside your combo boxes. You can use it inside of your list boxes, just like I've got here in my order form. All right, I use the query to create this combo box. If you want to see exactly how I built all of this, go watch my invoicing video. Again, I'll put a link down below. It's free. You can go watch it. I'll show you how I built this whole thing from the ground up. That's a free training on my website. If you want to learn more about concatenation, in the Extended Cut for members I talk about something called null propagation. That's where we can add null values together and get rid of that little comma space there if one of those names doesn't exist. That plus an if function. Not too hard to do. Then we'll talk about the format and trim functions. If you bring in a field like a phone number that you just had the digits stored in the table, you can still format it inside of your concatenation using the Format function. We can use the Trim function to trim off any leading or trailing spaces. Also, the same thing with currency. If you bring in a currency value, you just get the number 5000. It won't be formatted like a currency value. That's all covered in the Extended Cut for members. Silver members and up get access to all of the Extended Cut videos. How do you become a member? Click the Join button below the video. After you click the Join button, you'll see a list of all the different types of membership levels that are available. Silver members and up will get access to all of the Extended Cut TechHelp videos, live video and chat sessions, and more. Gold members get access to a download folder containing all the sample databases that I build in my TechHelp videos, plus my Code Vault where I keep tons of different functions that I use. Platinum members get all the previous perks plus access to my full beginner courses and some of my expert courses. These are the full length courses found on my website and not just for Access. I also teach Word, Excel, Visual Basic, ASP, and lots more. But don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more. If you liked this video, please give me a thumbs up and feel free to post any comments that you have. I do read them all. Make sure you subscribe to my channel, which is completely free, and click the bell icon and select All to receive notifications when new videos are posted. Click on the Show More link below the video to find additional resources and links. You'll see a list of other videos, additional information related to the current topic, free lessons, and lots more. YouTube no longer sends out email notifications when new videos are posted, so if you'd like to get an email every time I post a new video, click on the link to join my mailing list. Now, if you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of building databases with Access. It's over three hours long. You can find it on my website or on my YouTube channel. And if you like Level 1, Level 2 is just one dollar. And it's also free for all members of my YouTube channel at any level. Want to have your question answered in a video just like this one? Visit my TechHelp page and you can send me your question there. Click here to watch my free Access Beginner Level 1 course, more of my TechHelp videos, or to subscribe to my channel. Thanks for watching this video from AccessLearningZone.com. QuizQ1. What is string concatenation in Microsoft Access commonly used for?A. Combining two or more fields into a single value B. Copying fields from one table to another C. Deleting unwanted data from tables D. Importing data from Excel Q2. In the example from the video, which two customer fields were concatenated to display in a combo box? A. Address and phone number B. First name and last name C. Last name and first name D. Customer ID and last name Q3. What character was used to separate last name and first name in the concatenated field? A. Colon (:) B. Semicolon (;) C. Space only D. Comma and space (, ) Q4. Where can you use concatenated fields created in a query? A. Only in reports B. Only in tables C. In combo boxes, forms, queries, and reports D. Only in forms Q5. What do you call the new calculated field created in the query to concatenate last name and first name? A. Expression B. Alias C. Variable D. Table Q6. What operator is used in Access to concatenate (join) fields or strings together? A. Plus (+) B. Dash (-) C. Ampersand (&) D. Asterisk (*) Q7. When concatenating fields in a Control Source on a report, what is a sample formula to display Last Name, First Name? A. =LastName + ", " + FirstName B. =LastName / ", " / FirstName C. =LastName & ", " & FirstName D. =LastName - ", " - FirstName Q8. What is recommended regarding spaces in field and table names in Access? A. Always use spaces for readability B. Spaces are required C. Do not use spaces in names D. Spaces make no difference Q9. If you concatenate fields directly in a form or report, these calculated fields are: A. Editable like any other field B. Only for display, not editable C. Only available in queries D. Not visible to users Q10. What is "null propagation" as referenced for Extended Cut members? A. A method for sorting records alphabetically B. Handling null (empty) values when concatenating fields C. Creating new tables automatically D. Filtering data for reports Q11. What function can help format fields like phone numbers or currency when concatenating in Access? A. Lookup B. Sum C. Format D. Import Q12. Where is additional information, resources, and free lessons mentioned to be found? A. In the comments section only B. By clicking the Show More link below the video C. By resetting the database D. Only on the main YouTube homepage Answers: 1-A; 2-C; 3-D; 4-C; 5-B; 6-C; 7-C; 8-C; 9-B; 10-B; 11-C; 12-B 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. SummaryToday's video from Access Learning Zone focuses on showing you how to combine, or concatenate, multiple fields together in Microsoft Access. Specifically, I will demonstrate how you can perform this concatenation in a query, which you can then use inside a combo box. I will also explain how to do it directly within forms and reports.The topic for this lesson was inspired by a question from one of our Platinum members, who wanted to display both the first and last names in a customer combo box on an order form. Typically, combo boxes only display one field while closed, but it is possible to show a combined entry such as "LastName, FirstName" even though these are stored as separate fields in the customer table. To achieve this effect, you will need to use string concatenation. In my example, I start by using the TechHelp free template, which you can download from my website. On the customer form that leads to the orders section, you will see the customer combo box already displaying last name and first name together with a comma separating them. This concatenation is done with a query. I start by bringing in the customer table to the query's design view. In the query, I build a calculated field, which some of you may not have tried before. If this is new to you, I encourage you to check out my video on calculated query fields. For this scenario, I create a calculated field with an alias, such as LF, and concatenate the last name, a comma with a space, and then the first name. This approach displays the information exactly as desired: "LastName, FirstName." This combined field can then be referenced anywhere throughout your database, including in combo boxes, queries, forms, and reports. I provide much more detail about string concatenation in my Access Beginner Level 9 class, where I also show how you can combine fields directly on forms and reports. For example, you can place both last name and first name in a single field directly inside a report, without needing a query. To do this on a report, you can start with a blank report and connect it to your customer table as the record source. Add the first and last name fields to the detail section, align them, and delete any unnecessary fields or labels. After arranging your fields, you can create a new text box, name it something appropriate like LF for "LastFirst," and set its control source property to a concatenation formula using last name, comma, space, and first name. It's important to avoid spaces in your field and table names for smoother setup, as I explain fully in my free Access Beginner Level 1 class. When you preview the report, you will see the fields neatly combined. This method allows concatenation either at the query level or directly within forms and reports. Remember, though, if you use this formula in a form, the concatenated field will not be editable, as it is intended for display purposes only. However, it is extremely useful for combo boxes and list boxes, as demonstrated in my order form example. For a deeper understanding of this topic, including handling null values and formatting fields within your concatenations, I offer an Extended Cut for members. In the Extended Cut, I discuss null propagation, which ensures you do not end up with a stray comma if one of the name fields is empty. Additionally, I explain how to use the Format and Trim functions so you can present phone numbers, currency, and other fields in a friendly format right inside your concatenation formula. To access the Extended Cut and other Premium content, consider joining at the Silver level or higher. Silver members get access to all Extended Cut TechHelp videos, live sessions, and more. Gold membership adds downloadable sample databases and my Code Vault, while Platinum membership includes full access to all of my beginner and selected expert courses—not just Access, but also Word, Excel, Visual Basic, ASP, and more. Rest assured, these free TechHelp videos will continue as long as you keep watching and supporting them. If you found this lesson helpful, I would appreciate a thumbs up and your comments. Please make sure you subscribe to my channel, which is free, and set notifications so you never miss a new video. Check the Show More section for related links, lessons, and additional resources. Since YouTube no longer sends email alerts for new videos, you can join my mailing list if you want to receive notifications by email. If you are new to Access, my free Access Level 1 course is a great place to start. It covers the essentials of database building and is available on both my website and YouTube channel. Anyone interested in getting their own questions answered can submit them through the TechHelp page. For complete, step-by-step video tutorials covering everything discussed here, visit my website at the link below. Live long and prosper, my friends. Topic ListCombining fields using string concatenation in AccessCreating a calculated query field for concatenation Using concatenated fields in combo boxes Setting up concatenated display in forms Combining fields directly in reports without a query Assigning control sources with concatenation formulas Naming and organizing controls for concatenation Displaying concatenated fields for read-only purposes |
||||||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access concatenate, concatenation, concatenating, combine text values, combine strings, get rid of comma, null propagation, concatenate related rows, concatenate multiple records, concatenate fields in report, concatenate fields in form, join fie PermaLink Concatenation in Microsoft Access |
