Concatenation on two fields

  • Thread starter Thread starter KennyD
  • Start date Start date
K

KennyD

I have two text fields that I want to automatically concatenate together to
create a third text field. For example, text field 1 is a list of values
such as T1039, S0297, S0416, S2274. Text field 2 is a user input value with
an R concatenated on to the front of it. So, user might enter 001, but the
value needs to be stored as R001. But, then I need a Text Field 3 to store
the two inputs as TextField1&TextField2, i.e. T1039R014?
 
I have two text fields that I want to automatically concatenate together to
create a third text field.  For example, text field 1 is a list of values
such as T1039, S0297, S0416, S2274.  Text field 2 is a user input valuewith
an R concatenated on to the front of it.  So, user might enter 001, butthe
value needs to be stored as R001.  But, then I need a Text Field 3 to store
the two inputs as TextField1&TextField2, i.e. T1039R014?
Click to expand...

No you don't. Storing calculated values in your table is a bad idea.
If you want that concatenated field, do it in a query, not in your
table.

Keven Denen
 
Thanks for the quick reply. But I am sort of confused. Where would I enter
that? And how do I select those Text Fields? Would I use the Design Mode in
Access 2007 for the table where I want to store the value (it's the same
table as the two text fields)?

KARL DEWEY said:
Like this ---- [TextField1] & "R" & [TextField2]
--
Build a little, test a little.


KennyD said:
I have two text fields that I want to automatically concatenate together to
create a third text field. For example, text field 1 is a list of values
such as T1039, S0297, S0416, S2274. Text field 2 is a user input value with
an R concatenated on to the front of it. So, user might enter 001, but the
value needs to be stored as R001. But, then I need a Text Field 3 to store
the two inputs as TextField1&TextField2, i.e. T1039R014?
Click to expand...
Click to expand...
 
While I understand that you may think that it is a bad idea, I need to be
able to store the calculated value. So, how do I do it?
 
Okay. Let's see if we can approach this in a different manner.

I have two tables. Table 1 (named "tbl_Employees") has two fields, GroupNbr
(text field) and RepNbr (text field). The GroupNbr has a list of values that
the user can select from, such as T1039, S0297, S0416, S2274. The RepNbr
field is different based on the employee and which group they are working out
of (no duplicates allowed within the same group number). So, the user would
select the group number (say T1039) and enter the rep number (say 014).
(NOTE: the Rep number gets stored as R014, not just 014).

In Table 2 (named "tbl_Merchant"), I have a field that I need to enter the
concatenated value of the tbl_Employees.GroupNbr and tbl_Employees.RepNbr
(i.e. T1039R014). This value would need to be able to be selected from a
drop down menu.

The concatenated value is what needs to be stored in Table 2
("tbl_Merchant"). How would I accomplish this? Thanks for the help.
 
While I understand that you may think that it is a bad idea, I need to be
able to store the calculated value.  So, how do I do it?
--
Nothing in life is ever easy - just get used to that fact.







- Show quoted text -
Click to expand...

Why do you need to store the calculated value? If the components are
available to you, you can always calculate them whenever you need it.

Keven Denen
 
Why do you need to store the calculated value? If the components are
available to you, you can always calculate them whenever you need it.

Keven Denen- Hide quoted text -

- Show quoted text -
Click to expand...

So you are using those values to populate a drop-down? If this is the
case, use a query to concatenate the values together, then use that
query as the row source for your drop-down.

Keven Denen
 
I have two text fields that I want to automatically concatenate together to
create a third text field. For example, text field 1 is a list of values
such as T1039, S0297, S0416, S2274. Text field 2 is a user input value with
an R concatenated on to the front of it. So, user might enter 001, but the
value needs to be stored as R001. But, then I need a Text Field 3 to store
the two inputs as TextField1&TextField2, i.e. T1039R014?
Click to expand...

Actually, you should NOT store this field in any table. If it depends on the
first two fields, just calculate it on the fly in a Query by typing:

Field3: [Field1] & "R" & [Field2]

in a vacant Field cell in the query design grid, or

= [Field1] & "R" & [Field2]

in the Control Source of a textbox on a form or report.

Storing this value in a field in a Table will not only waste disk space and
waste time (a disk fetch is MUCH slower than a concatenation calculation!) but
will risk data corruption; since you're storing the same data in two different
fields, both editable, one of them or other could be changed leaving it
inconsistant with the other.
 
I need the stored value because I have to export the record with the stored
value.
Click to expand...

You can export from a calculated field in a Query *just* as easily as you can
export from a Table.
 
Back
Top