98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsPrevious article Next article
Access articles
How To Use Concatenation In Access To Create A Custom Lookup
Sat 23rd April 2011
If you want to carry out calculations or operations on data stored in an Access table you can only do this in a query, form or report rather than in the actual table. We'll describe using concatenation in a query in this article. Suppose you have a staff table containing the four fields Ref, Title, Firstname and Surname. Ref is an autonumber field and is the primary key. You want to create a Combo Lookup to use in another table which shows each member of staff's title, firstname and surname all in the lookup list and adds the appropriate staff Ref number into the table.
You want the Combo lookup to show the full names to ensure the correct member of staff is selected by the Combo. This can be achieved by using a query based on the first table to create a custom field using concatenation. The custom field can then be used as the Combo lookup list in the second table.
To create the custom lookup, first create a query based on the staff table and add the four fields Ref, Title, Firstname and Surname to the query builder. Then create a new fourth field in the query with the label FULLNAME followed by a colon and then use concatenation to combine data from the other three fields. Concatenation uses the ampersand symbol & to combine text fields together in a new field. So the expression entered for the new fourth field is FULLNAME:Title & Firstname & Surname and then click into the next cell down to let Access automatically add square brackets round the field names. You don't need to type spaces before and after the ampersand symbol as Access will also do this for you.
However, running this query will produce a list which contains data such as 1 MrsMarySmith, 2 MrBillGreen and 3 MrsJaneJones. To add spaces between the parts we return to the query design view and amend the expression to include spaces. This is done using additional ampersands and " ". Note: There's one spacebar press between each quote mark. So the amended fourth field becomes FULLNAME:[Title] & " " & [Firstname] &" " & [Surname] and then click one cell down to let Access tidy up the spacing.
Then run the query and this time you should see each record showing as Mrs Mary Smith, Mr Bill Green, Mrs Jane Jones and so on. We're almost there. Switch back to query design view, and untick the show checkboxes for all the fields except Ref and Fullname. the custom one, as we only want to see the Fullname field. Run the query again and this time you'll see just the Ref and the custom field, which is perfect for our Combo lookup. Save the query with an appropriate name such as QryFullnames.
Next we're going to use this query in a Combo control to allow us to select members of staff from the lookup list and add to another table, for example to record which member of staff attended a particular training course.
So in this other table, in design view, we create a Staff field and set the type to numerical, to match the Ref field type. Then with this new field selected, look in the properties in the lower part of screen and select the Lookup tab. Then choose the display control Combo. Leave Row Source type as Table/Query, and set the Row Source to your new query, QryFullnames, then save the table to finish. Now in regular table view test the new combo and ensure the pop down shows the data from our query QryFullnames. We see the names in the lookup list but we actually add the staff Ref number into the new table. And that completes the building of the Combo lookup using concatenation.
What to find out more about using queries and lookups in Access? A really effective way is to attend a training course as this can be really effective in boosting your skills in a short time.
Author is a freelance copywriter. For more information on microsoft access courses london, please visit https://www.stl-training.co.uk
Original article appears here:
https://www.stl-training.co.uk/article-1616-how-use-concatenation-in-access-create-custom-lookup.html

London's widest choice in
dates, venues, and prices
Public Schedule:
On-site / Closed company:
TestimonialsPukka Herbs
Sales Manager Shamini Singh Excel Intermediate The trainer is very good and helping understand the programme. I felt at ease when I made errors and knew that I had the space to work through it supported. MSAmlin
Underwriter Mack Williams Excel Advanced Atlas has great knowledge and provides useful insight. Liberata UK Limited
Client Support Manager Uzma Ahmed Excel Intermediate Found the course really useful with easy to follow instructions at the perfect pace |
PUBLICATION GUIDELINES