Menu
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.
435
Use the & Operator Yes, instead of using CONCATENATE, you can use the ampersand operator — & — to combine cell values in Excel.
When you want to combine the values in two or more text fields in Access, you create an expression that uses the ampersand (&) operator.
Click the "Arrange" tab, and then click the "Merge" button in the Merge/Split group to merge your selected fields into one.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
