Concatenation

[krisboy]

Hi all!

In need of some more help....

I need to be able to join the contents of 3 different fields into one new field, as I'm trying to create call numbers, which are made up from the amalgamation of region_number, client_no and ref_no. (The rather deficient storing of this information necessitates me doing this). Would anyone know how to concatenate these three fields in Access? (I know it can be done in MS Excel using the CONCATENATE function to join information contained in cells....)

Many Thanks

Chris.

 

[LittleSmudge]

Yes it can be done, and the HOW depends on the WHERE you want to do it.
( Although I'll point out before anyone else does that if a control on a Form or Report is simply an amalgamation ( concatenation ) of other fields then it should not be stored in the table - it should be calculated each time it is needed. ) Now that Codd is out of the way - to the answer.. ..

If you have a Form open then have a control on the form bound to the Concatenated field in the table.
Then in the AfterUpdate event for each of the source fields put in code that does:-

txtConcat = Nz(region_number,"&quot & strDelim & Nz(client_no,"&quot & strDelim & Nz(ref_no,"&quot

where strDelim is the string variable containing whatever delimitor characters you want between the components
The Nz(.. .. .. ,"&quot simply protects you from Null values in controls that haven't had any data put into them yet.


If you haven't got a Form open ( eg the data is being populated mechanically from another source ) then that populating mechanism will have to use the same string concatenation but open a recordset to write to the field, as in
rst!strConcat = Nz(region_number,"&quot & strDelim & Nz(client_no,"&quot & strDelim & Nz(ref_no,"&quot



'ope-that-'elps

G LS

 

[krisboy]

Very useful info - thanks!

One more thing - Is it possible to perform this type of operation in a query?

Cheers.

Chris.

 

[LittleSmudge]

You could do it in an UPDATE query yes.

From the Access Query design window get the target field in the grid then change the query type to UPDATE and then place the code as above into the Update To line in the correct column.

Then Action the Query


G LS