How to combine five data fields into one string for a new field

[datsandgirl]

First of all, please let me apologize in advance for my lack of technical knowledge and proper terminology skills when it comes to databases. I have never taken any classes, but have only felt my way around by trial and error, hook and by crook, and mimicry. In other words, I have just enough knowledge to know I don’t know anything and to get me into trouble. But here goes…

I am using Access 2007. I have five fields in a table named TblComponents. The fields are named:

[OpportunityRef] – a company-assigned reference number
[Customer] – Control Source for the 1st of 3 cascading combo boxes called cboCustomers in the bound form named Components
[Division] – Control Source for the 2nd of 3 cascading combo boxes called cboDivisions
[Location] – Control Source for the 3rd of 3 cascading combo boxes called cboLocations
[Program] – text usually consisting of many words

Besides needing these fields as individual pieces of data for each of our products or “components” throughout the database, our company uses a unique tag name to reference each component in all correspondence. This tag name is made up from a string of identifying data and is joined together with dashes, namely [OpportunityRef]–[Customer]–[Division]–[Location]–[Program]. So a tag name might look like this:

0098457–All Star Systems–Technical Services–Dallas, TX–Zebra Flight Struts/Multi-Mission Project

I would like to create a field which is automatically populated from the five table fields, with dashes added in between, which effectively builds the tag name for the user. I would like to be able to access this field from both the table itself and the form.

Is this possible? How would I accomplish this?

I would also like to ask another question. Our company uses a lot of dashes and slashes in our daily written business. Do these characters create problems in Access. If so, is there a work around for that so to speak?

Thanks in advance for any and all help received!

[dpatel1682]

You can use the following [OpportunityRef]&"–"&[Customer]&"–"&[Division]&"–"&[Location]&"–"&[Program].
Also, regarding - and /, it all depends on what you are planning to do

[datsandgirl]

Thanks for your quick reply! I hate to sound even more ignorant, but on what, how, and where would I use what you typed? I'm afraid I wasn't kidding about not knowing anything.

Thanks!

[NeoPa]

It is possible to do what you request, but rarely recommended as it introduces a level of de-normalisation into the design (See Normalisation and Table structures). Let me know if you feel you absolutely must duplicate this data and I will help anyway.

An alternative approach would be to use an unbound TextBox (or Label even depending on your actual usage) on your form, and any other object that requires this same value, which is made up of the concatenated values of the other controls as you require (similar in many respects to what DPatel suggested).

The Control Source would be something like :

=[OpportunityRef] & '-' &
 [cboCustomers] & '-' &
 [cboDivisions] & '-' &
 [cboLocations] & '-' &
 [Program]

I've shown it on separate lines just to display easily but in normal usage this would be a single line.

[dpatel1682]

@datsandgirl
You can do this either in a new query or if you have a field in a table called "TAG" then do this in an update qry

[NeoPa]

dpatel1682: or if you have a field in a table called "TAG" then do this in an update qry

Or not. This is not recommended for the reasons explained in the link.

[datsandgirl]

@NeoPa
Beautiful, perfect NeoPa! This is just what I needed. The biggest purpose of this request was to provide a way for the user to be able to quickly and easily copy and paste this combersome tag name into correspondence. Thank you and dpatel for all your time and help!

[NeoPa]

You're very welcome :)

I'm pleased we were able to help.

[NeoPa]

A new thread has been split from this one and deals with the question of Cascading Filtering.

Any interested parties can find it there.