CODEWITHSUNDEEP
ms-accessvba
Karthik
Karthik

Reputation: 149

How to remove particular character in a column in ms access table

I have a ms access table which has a column named description which contains several records, There is a Space in the records which are of longer lengths, I need to remove the space which is in 26th character. As i am new to access i find difficult to write queries, Any help would be greatly appreciated. For Example descrption column has few values like this : MOXIFLOXACIN HYDROCHLORDI E/SODIUM CHLORIDE, There is a space between I and E, SO i need to concatenate it like this for all the records

MOXIFLOXACIN HYDROCHLORDIE/SODIUM CHLORIDE

Upvotes: 1

Views: 1310

Answers (1)

Erik A
Erik A

Reputation: 32682

Removing characters can be a difficult concept.

If you want to remove the 26th character, you essentially should take all characters except the 26th character, which is the 25 characters to the left, and all characters higher than 27 to the right.

I'm checking inline if the string is longer than 26 characters, you might also decide to check that in a WHERE clause.

UPDATE myTable
Set MyColumn = Left(MyColumn, 25) & IIF(Len(MyColumn) > 26, Right(MyColumn, Len(MyColumn) - 26), "")

Upvotes: 2

Related Questions