Join two fields in one table 1

[DECNET]

Here's my question, is it possible to merge two fields within the same Access table.
For instance I have two Fields 'Site ID' and 'Site Number' can these two fields be merged to form data in a third column.
IE Site ID=ABCD
Site Number=1234
Result column=ABCD1234

I can do it easily in Excel, in cell C3 type the formula A1&B1 and hey presto, whatever data I type into A1 and B1 are joined in C1. But it doesn't seem to be that easy in Access.
I have managed to do it in a Form using data from the Table and the result displays in the Form, but does not update the Table.

Where am I going wrong? DEC

If it helps, let me know, if it doesn't, let me know. It's the only way I'll learn.

 

[LarryDeLaruelle]

DEC:

It's a similar process in Access.

Add the new field to the table structure.

Create an update query based on the table. Add just that field to the query.

In the update to cell type in [SiteID] & [SiteNumber].

If the site number is a numeric field you have an additional complication and will need to convert it to text using the Str function

[SiteID] & Str([SiteNumber])

I believe that the Str function will place a space in front of the conversion for the plus/minus sign. If so, I think this will work

[SiteID] & LTrim(Str([SiteNumber])

Try it out. Let me know if you have problems.
Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[DECNET]

Thats brilliant.

Works well on some test data, will try on the real thing tomorrow.

I'll let you know how I get on.

Thanks DEC

If it helps, let me know, if it doesn't, let me know. It's the only way I'll learn.

 

[DECNET]

Now in place on the 'real' database and working well.

Cheers Larry DEC

If it helps, let me know, if it doesn't, let me know. It's the only way I'll learn.