Toad World® Forums

Concatenate two columns in an Access Table when creating a calculated field

I have imported two tables in an Access database.
One table has a single column which holds the a persons’ surname, forename e.g. [Jenkinson, David].
While the second table (named LAWTOK) has two separate columns (one for surname and one for forename).

These are the only common fields in the data that can be used to join the tables together.

I have tried without success to create a calculated field on the second table to concatenate a persons’ surname and forename together…

In the calculated field definition I have tried two versions:

LAWTOK.Surname & ", " & LAWTOK.Forename

concat(LAWTOK.Surname, ', ', LAWTOK.Forename)

I get the following Error Message:
[Microsoft][ODBC Microsoft Access Driver] The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

Your help would be most welcome.

Thank you in advance.

Regards

David

Are you using TDA 3.0? If so I would use Data Cleansing. Here you can create a calculated field by using the concat function. See example below.

Concat(Concat([FirstName], ', '), [LastName])

Debbie

Hello Debbie,

I am using TDA 3.0 Base Edition, so don’t have Data Cleansing functionality…

Is there another way to achieve this in my version of TDA?

Regards

David

Did you try this with a single quote? This works in 3.0 Pro when combining two fields in Access…

LAWTOK.Surname & ’ , ’ & LAWTOK.Forename

No I hadn’t tried that and it works .

Thanks again…

David

select (LAST_NAME & ', ’ & FIRST_NAME) as name from CONTACT

Debbie