CONCAT equivalent in MS Access

by

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

More Related Contents:

  1. How to simulate UNPIVOT in Access?
  2. Top n records per group sql in access
  3. Achieving ROW_NUMBER / PARTITION BY in MS Access
  4. How do I use parameters in VBA in the different contexts in Microsoft Access?
  5. Upserting in MS-access
  6. How to get matching data from another SQL table for two different columns: Inner Join and/or Union?
  7. Case expressions in Access
  8. SQL Query – Using Order By in UNION
  9. How do I count unique items in field in Access query?
  10. Date Difference between consecutive rows
  11. Pivoting data in MS Access
  12. Using Excel VBA to export data to MS Access table
  13. Access-SQL: Inner Join with multiple tables
  14. Address standardization within a database
  15. Does MS Access support “CASE WHEN” clause if connect with ODBC?
  16. Why does a LIKE query in Access not return any records?
  17. How to SUM two fields within an SQL query
  18. How to use system username directly in MS Access query?
  19. Query for getting value from another record in same table and filter by difference greater than a gap threshold
  20. SQL Updating from an inner join
  21. How to delete in MS Access when using JOIN’s?
  22. Lightweight SQL database which doesn’t require installation [closed]
  23. HOW TO SELECT PREVIOUS ROW VALUE? [duplicate]
  24. Editing Record issues in Access / SQL (Write Conflict)
  25. How do I create a table alias in MySQL
  26. How to save the result of a SQL query into a variable in VBA?
  27. Select query with date condition
  28. Does MS access(2003) have anything comparable to Stored procedure. I want to run a complex query in MS acceess
  29. Microsoft Access – Case Query
  30. Check if access table exists

Leave a Comment