Concatenation on two fields

[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?

 

[KARL DEWEY]

Like this ---- [TextField1] & "R" & [TextField2]

 

[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 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?

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

 

[KennyD]

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)?

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

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?

 

[KennyD]

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?

 

[KennyD]

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.

 

[Keven Denen]

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 -

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

 

[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 -

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

 

[KennyD]

I need the stored value because I have to export the record with the stored
value.

 

[John W. Vinson]

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?

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.

 

[John W. Vinson]

I need the stored value because I have to export the record with the stored
value.

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