How do you Concatonate 2 text boxes on a form and putting the result into a table?

[gobarr]

I'm trying to concatenate 2 fields on a form and put the result into
table. Is there a way to do this without code? I'm not familia
enough with SQL

 

[Nikos Yannacopoulos]

If you don't want to use VB code:
1.Make a new text box on your form and set its control source property to
the table field you want to put the result to (it can be hidden if you don't
want it showing on the form; you can set its properties Enabled > No, Locked

Yes to prevent users from manually changing it.)

2.Make a simple macro that uses the SetValue action, with the following
arguments:
Item: Name of the new text box
Expression: =[Text1] & [Text2] (where Text1 and Text2 are the names of the
two text boxes to be concatenated)
3.In the properties of the two text boxes to be concatenated, Events tab,
Event On Change, select the macro you just made.

Actually what this does is each time either source text box is changed, the
new concatenated text will be put in the bound text box.

HTH,
Nikos

 

[John Vinson]

I'm trying to concatenate 2 fields on a form and put the result into a
table. Is there a way to do this without code? I'm not familiar
enough with SQL.

Nikos has a Macro based solution... but I have a concern. It sounds
like you're storing data redundantly.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

If you want to have the two fields constitute a joint Primary Key,
bear in mind that a PK can consist of up to TEN fields - it's neither
necessary nor prudent to create a third combined field.

If (all that said) you want to store the concateneated field anyway
Nikos' suggestion should work fine.

 

[Dan Stenzel]

I have a similar problem.
I have multiple fields to put into a mailing label
(prefix, first-name, middle-initial, last-name, suffix)
Where not all of them will always be present.

Is there a simple to way to create a re-usable concatenation for this
field so that I don't have to recreate it every time?

Thanks

 

[John Vinson]

I have a similar problem.
I have multiple fields to put into a mailing label
(prefix, first-name, middle-initial, last-name, suffix)
Where not all of them will always be present.

Is there a simple to way to create a re-usable concatenation for this
field so that I don't have to recreate it every time?

You can take advantage of the subtle difference between the two
concatenation operators + and &. & treats a NULL as a zero length
string; + "propagates nulls" returning NULL if either argument is
NULL.

Try

([Prefix] + " ") & ([first-name] + " ") & ([middle-initial] + " ") &
[Last-name] & (" " + [suffix])

Each expression in parentheses will evaluate to NULL - suppressing any
extra blanks - if the field is NULL.