Concatenate Conditional Field Paper For Free

concatenation joins one or more strings of information together which means in Access you can join or concatenate information stored in separate Fields together in one new field and you can concatenate from several different objects you can concatenate from queries or from forms or reports but no matter what the object the symbol you use to join fields or text strings together will always be the Amper sand to give you an example of how to concatenate we're going to be working with information from our contacts table and concatenating the first name middle initial and last name fields to display our contacts full name so jumping back into design view in the blank field slot let's begin by typing out what we want to call this particular field and in this case it's going to be full name followed by a colon next we want to begin typing out our expression or what fields we want to join together and in this case we want to begin by typing the reference to the first name field so Open Bracket first name close bracket and we're joining that with the middle initial field so typing the Amper sand symbol and then the reference to middle initial close bracket and then again we're joining that Ampersand with the reference to the last name field and just to show you what that looks like so far let's go ahead and run this query and it works it is joining together all the information from those three separate Fields however it doesn't really look that pretty and that's because we haven't told access to add spaces between the information that's stored in each of those fields so jumping back into design view in between each individual field reference we want to tell access to also concatenate a space so right after that first name field I'm going to type another Ampersand and in quotation marks I'm going to type a space and close quotes same thing right after that middle initial field another Amper stand and this time I'm going to type in open quotation marks period space close quotes so that's what it should look like so far and when we run this query for the most part it looks pretty good except for when we have contacts who do not have a middle initial notice what axis is still doing it's still adding that period and an extra space which of course we don't want we want that optional if the middle initial field is null now there are a couple of ways to get the results that we are looking for we we could use the IF function to tell axis to concatenate one way if there's something typed into that mil initial field and can catenate another way if middle initial is null or we can use a slightly easier method which is adding or using the plus sign around our middle initial fields to indicate when you want null returned if a field isn't filled in so jumping back into design view we're going to edit this ever so slightly right after that first Ampersand I'm going to begin by typing an open parentheses and then instead of that Ampersand I'm going to use that plus sign...