CONCAT equivalent in MS Access

Question

I'm doing some work in MS Access and I need to append a prefix to a bunch of fields, I know SQL but it doesn't quite seem to work the same in Access

Basically I need this translated to a command that will work in access:

UPDATE myTable
SET [My Column] = CONCAT ("Prefix ", [My Column]) 
WHERE [Different Column]='someValue';

I've searched up and down and can't seem to find a simple translation.

score 16 · Answer 1 · answered Dec 05 '13 at 15:52

There are two concatenation operators available in Access: +; and &. They differ in how they deal with Null.

"foo" + Null returns Null

"foo" & Null returns "foo"

So if you want to update Null [My Column] fields to contain "Prefix " afterwards, use ...

SET [My Column] = "Prefix " & [My Column]

But if you prefer to leave it as Null, you could use the + operator instead ...

SET [My Column] = "Prefix " + [My Column]

However, in the second case, you could revise the WHERE clause to ignore rows where [My Column] contains Null.

WHERE [Different Column]='someValue' AND [My Column] Is Not Null

Ah, I had not realized there was a difference in the two, thanks HansUp, this is good to know as I do not want to Null out all my values! — salty, Dec 05 '13 at 15:57

score 13 · Accepted Answer · answered Dec 05 '13 at 15:29

13

UPDATE myTable
SET [My Column] = "Prefix " & [My Column] 
WHERE [Different Column]='someValue';

As far as I am aware there is no CONCAT

answered Dec 05 '13 at 15:29

Fred

Thanks Fred, worked perfectly. It's weird that ACCESS doesn't allow all SQL functions. – salty Dec 05 '13 at 15:51

score 2 · Answer 3 · answered Dec 05 '13 at 15:28

2

You can use the & operator:

UPDATE myTable
    SET [My Column] = "Prefix " & [My Column]
    WHERE [Different Column]='someValue';

answered Dec 05 '13 at 15:28

Gordon Linoff

score 2 · Answer 4 · answered Dec 05 '13 at 15:31

2

Since there is no Concat function in MS-ACCESS, you can simply combine both strings with + operator:

  UPDATE myTable
 SET [My Column] = "Prefix " + [My Column]
 WHERE [Different Column]='someValue';

answered Dec 05 '13 at 15:31

Kiril Rusev

2

For string concatination you should use & as it explicitly a string operation. The plus sign could give unexpected results – Fred Dec 05 '13 at 15:42

4 Answers4